Skip to content

Instantly share code, notes, and snippets.

@arielvalentin

arielvalentin/txt

Created August 23, 2017 15:03
Download ZIP

Revisions

  1. arielvalentin created this gist Aug 23, 2017.
    41 changes: 41 additions & 0 deletions txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,41 @@
    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