Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --sample purchases to verify fields
- select
- a.athlete_id,
- date_trunc('d',a.timestamp) as purchase_date,
- a.platform_id,
- a.sku,
- b.from_trial,
- b.subscription_type,
- b.subscriber_type,
- b.platform
- from
- event_premium_purchase_success a
- left outer join
- subscribe_events b
- on
- a.athlete_id = b.athlete_id
- AND date_diff('d',date_trunc('d',a.timestamp),b.date) between 0 and 1
- order by
- date_trunc('d',timestamp)
- limit 100;
- --sample failed purchases
- select
- a.athlete_id,
- date_trunc('d',a.timestamp) as purchase_date,
- a.platform_id,
- a.sku,
- max(case when b.athlete_id is not null then 1 else 0 end) as in_subscribe_events
- from
- event_premium_purchase_success a
- left outer join
- subscribe_events b
- on
- a.athlete_id = b.athlete_id
- AND date_diff('d',date_trunc('d',a.timestamp),b.date) between 0 and 1
- group by
- 1,2,3,4
- having
- b.athlete_id is null
- order by
- date_trunc('d',timestamp);
- select
- a.athlete_id,
- date_trunc('d',a.timestamp) as purchase_date,
- a.platform_id,
- a.sku,
- b.*
- from
- event_premium_purchase_success a
- left outer join
- subscribe_events b
- on
- a.athlete_id = b.athlete_id
- AND date_diff('d',date_trunc('d',a.timestamp),b.date) between 0 and 1
- where
- a.athlete_id = 5741909
- order by
- a.timestamp;
- --aggregated purchase record success rates
- select
- purchase_date,
- platform_id,
- sku,
- sum(in_subscribe_events),
- count(*),
- sum(in_subscribe_events)*1.00/count(*) as avg_in_sub_events
- from
- (
- select
- a.athlete_id,
- date_trunc('d',a.timestamp) as purchase_date,
- a.platform_id,
- a.sku,
- max(case when b.athlete_id is not null then 1 else 0 end) as in_subscribe_events
- from
- event_premium_purchase_success a
- left outer join
- subscribe_events b
- on
- a.athlete_id = b.athlete_id
- AND date_diff('d',date_trunc('d',a.timestamp),b.date) between 0 and 1
- group by
- 1,2,3,4
- order by
- date_trunc('d',timestamp)
- )
- group by
- 1,2,3
- order by
- 1,2,3
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement