New Query

New_Query.sql

with disable_firebase_users as (
    select
        *
    from
        cookpad_tv.users ctu
    join cookpad_tv.financier_subscriptions ctfs on ctu.id = ctfs.user_id
    left outer join cookpad.secure_users csu on ctu.cookpad_user_id = csu.user_id
    left outer join cookpad.secure_user_phone_numbers csupn on ctu.cookpad_user_id = csupn.user_id
    where
        ctu.cookpad_user_id is not null
        and ctu.use_firebase_authentication = false
        and ( csu.email is not null or csupn.phone_number is not null )
), billing_user_counts as (
    select
        0 as "all_count"
        , case
            when financier_product_code in ('cookpad_tv_gold_membership', 'cookpad_tv_gold_membership_2023') then 1
            else 0
        end as "gold_membership"
        , case
            when financier_product_code = 'cookpad_live_platinum_membership' then 1
            else 0
        end as "platinum_membership"
    from
        disable_firebase_users
    where
        activated_at is not null
        and cancelled_at is null
        and financier_product_code in ('cookpad_tv_gold_membership', 'cookpad_tv_gold_membership_2023', 'cookpad_live_platinum_membership')
), all_user_counts as (
    select
        1 as "all_count"
        , 0 as "gold_membership"
        , 0 as "platinum_membership"
    from
        disable_firebase_users
)

select
    sum(all_users.all_count) as "全体"
    , sum(all_users.gold_membership) as "GM"
    , sum(all_users.platinum_membership) as "PM"
from (
    select
        *
    from
        billing_user_counts 
    union all
    select
        *
    from
        all_user_counts
) as all_users
;

New_Query_02.tsv

New_Query_03.md

Data source

key	value
type	Redshift
host	dwh.ckpd.co
port	5439
user	naoyashibahara
database	production

Created by

Bdash