CODEWITHSUNDEEP
sqlaggregate
Kumar
Kumar

Reputation: 119

how to calculate data at daily weekly and monthly granularity in one sql

I have data in this format on S3 I am using athena to generate aggregate results after join on 2 key columns

[1]: https://i.sstatic.net/OCUCe.png

Requirement -

At country level, I have to calculate count of events at daily, weekly and monthly level So for japan in month of oct two events, weekly one event should show as result.

Expected result -

when i join on country and event_date == transaction_date

enter image description here how do I do the same in SQL

Upvotes: 0

Views: 900

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270331

Hmmm . . . If I am following your question you can use window functions in a subquery and then filtering:

select t.*
from (select t.*,
             count(*) over (partition by eventdate) as day_count,
             count(*) over (partition by week) as week_count,
             count(*) over (partition by month) as month_count
      from t
     ) t
where eventdate = :transactiondate

Upvotes: 1

Related Questions