Orders, revenues and Units value

[Aravind5757]

In snowflakes while doing  units and Price calculation we are getting the alphabetical character .

However if we put the below condition we are not getting the alphabetical character.

SELECT rows WHERE event_list EQUALS TO 1

Is this mandatory condition to give for doing the price calculation?

[Aravind5757]

@jenniferduncan 

[Jennifer_Dungan · Accepted solution]

Hi @Aravind5757 ,

I don't really use Snowflake in our company, that's technically another team...

However, if you are limiting your rows to only those where the event_list equals 1, is that you will only see the rows where that is the only event that is being triggered... which might work for you, it might not... I don't know enough about your implementation... in our site, that would be a big problem, since our purchase action contains multiple events. Also, are you setting any events in processing rules, because if you are looking at event_list and not post_event_list, you might be missing additional rows of data. (post_**** are the values that you get in Adobe, after all the processing has been run).

You should check out this post:
https://experienceleaguecommunities.adobe.com/t5/adobe-analytics-blogs/sample-sql-queries-to-query-a...

But for brevity, I will copy the SQL here:

SELECT COUNT(*) 
FROM <your_data_feed_name> 
WHERE (post_event_list LIKE '%,1,%' OR post_event_list LIKE '1,%' OR post_event_list LIKE '%,1') 
AND duplicate_purchases != '1'

This will get the rows where the list contains 1 (not 10, not 11, not 100, etc)... but it does look for the varieties of starting with that event, ending with that event, or that event being somewhere in the middle.

I assume that your query was having issues because you were trying to extract quantity and price from rows that weren't purchases...  so limiting it to only the purchase rows makes sense, but it just needs a little tweaking to ensure that you get all the potential variations.

[Aravind5757]

Thanks Jennifer this helps