Last active
August 29, 2015 14:25
-
-
Save quarterdome/292785b60a685a600cba to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| with | |
| merged as ( | |
| select | |
| event_id, | |
| sent_at, | |
| name, | |
| campaign, | |
| null as interest_id, | |
| 1 as is_first_event | |
| from web.traffic_source | |
| where sent_at > '2015-07-21' | |
| union | |
| select | |
| event_id, | |
| sent_at, | |
| null as name, | |
| null as campaign, | |
| interest_id, | |
| 0 as is_first_event | |
| from api.interest | |
| where | |
| source_app in ('TabletApartmentList', 'ApartmentList', 'MobileApartmentList') | |
| and | |
| sent_at > '2015-07-21' | |
| ), | |
| interest_with_session as ( | |
| select | |
| event_id, | |
| sent_at, | |
| interest_id, | |
| sum(is_first_event) over | |
| ( partition by user_id | |
| order by sent_at | |
| rows between unbounded preceding and current row ) as session_id | |
| from merged | |
| ) | |
| select | |
| *, | |
| first_value() over | |
| ( partition by user_id, session_id | |
| order by sent_at) as interest_campaign | |
| from interest_with_session | |
| ; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment