SQL to print the result in below format

user_1773702 0
Viewed 34

I have an application where we use AWS Athena. I have 2 tables viz. events and event_transactions. events table contain event information and event_transactions contain individual events and there is a column event_date which tells the day on which event occurred.

I need to calculate the count of events for each event for last 1 month interval, last 1 week interval and last 1 day from today's date.

Format:

event_name, daily_count, weekly_count, monthly_count

I need to display all 3 counts for each event in the same row.

To calculate weekly_count I use below query:

select event_name, count(*) as weekly_count from event_transactions where event_name in ('ABC','XYZ')
and (event_date >= CAST(current_date - interval '7' day as varchar)) AND (event_date <= CAST(current_date - interval '1' day as varchar)) 
group by 1

Output:

event_name.    weekly_count
ABC.           23
XYZ.           14

How can I write a SQL query which will print all 3 counts in a single row?

presto amazon-athena sql
1 Answers

Use count_if. Something along this lines:

select event_name, 
    count_if(event_date >= CAST(current_date - interval '7' day as varchar) AND event_date <= CAST(current_date - interval '1' day as varchar)) as weekly_count ,
    ... -- rest of the counts
from event_transactions 
where event_name in ('ABC','XYZ')
group by 1

Also I would recommend looking into between range operator and using date_parse on event_date if it has data in consistent format.

user_25012790
Related