Skip to content

Instantly share code, notes, and snippets.

@arielvalentin
Created August 23, 2017 15:03
Show Gist options
  • Save arielvalentin/508c712353f1fa586875646d4d7ad542 to your computer and use it in GitHub Desktop.
Save arielvalentin/508c712353f1fa586875646d4d7ad542 to your computer and use it in GitHub Desktop.
Redshift Random Characters
psql> with
ld as (select distinct date_trunc('day', (day)::timestamp)::date as date, first_value(day) over (partition by date_trunc('day', (day)::timestamp)::date order by "day" desc rows unbounded preceding) as last_day from events.stripe_logic_views.ten_years_in_days_from_first_invoice where 1=1
) select
date
, add_on_plan_id
, sum(amortized_total_amount / 100) as total_mrr
from
stripe_logic_views.daily_mrr_by_customer d
join ld on
ld.last_day = d.day
and 1=1
and 1=1
and add_on_plan_id is not null
group by
1,2
order by
1,2
limit 5000;
date | add_on_plan_id | total_mrr
------------+---------------------------------------------+-----------
2017-06-22 | H | 99
2017-06-23 | D | 99
2017-06-24 | D | 99
2017-06-25 | D | 99
2017-06-26 | H | 99
2017-06-27 | D | 99
2017-06-28 | D | 99
2017-06-29 | H | 99
2017-06-30 | launch-financial-mgmt|3|pre-revenue|yearly | 99
2017-07-01 | D | 99
2017-07-02 | H | 99
2017-07-03 | H | 99
2017-07-04 | D | 99
2017-07-05 | launch-financial-mgmt|3|pre-revenue|yearly | 99
2017-07-06 | launch-financial-mgmt|3|pre-revenue|yearly | 99
2017-07-07 | D | 99
2017-07-07 | launch-financial-mgmt|3|pre-revenue|monthly | 119
2017-07-08 | H | 99
2017-07-09 | H | 99
2017-07-10 | launch-financial-mgmt|3|pre-revenue|yearly | 99
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment