ohmy.sql

%%sql
WITH debug_user_window AS (
    WITH debug_user AS (
        SELECT 
            id, 
            amount, 
            date, 
            user_id, 
            subscription_id
        FROM 
            transactions as t
        WHERE 
            subscription_id in (SELECT id
                        FROM subscriptions 
                        WHERE 
                            service_id = (SELECT id 
                                          From services
                                          WHERE name = 'Netflix')
                        LIMIT 1)
        ORDER BY
            date
    )
    SELECT
        *,
        date - lag(date) over(order by date) as delta
    FROM 
        debug_user
)
SELECT
    min(user_id) as user_id,
    min(subscription_id) as subscription_id,
    count(*),
    min(amount)/100.0 as amount_min, 
    max(amount)/100.0 as amount_max,
    avg(amount)/100.0 as amount_mean,
    min(date) as date_min,
    max(date) as date_max,
    months_between(min(date),max(date)) as date_delta,
    min(delta) as delta_min,
    max(delta) as delta_max,
    avg(delta) as delta_mean
FROM 
    debug_user_window
;