segment-sql-v1.sql

with

shop_user(id, name, registered_at) as (
  values
    (1, '33maki', '2020-01-01' at time zone 'jst')
    , (2, '23maki', '2020-01-06' at time zone 'jst')
    , (3, '13maki', '2020-01-08' at time zone 'jst')
    , (4, '25maki', '2020-01-14' at time zone 'jst')
    , (5, '36maki', '2020-01-15' at time zone 'jst')
    , (6, '21maki', '2020-01-17' at time zone 'jst')
    , (7, '32maki', '2020-01-22' at time zone 'jst')
    , (8, '34maki', '2020-01-25' at time zone 'jst')
    , (9, '12maki', '2020-01-26' at time zone 'jst')
    , (10, '14maki', '2020-01-28' at time zone 'jst')
    , (11, '16maki', '2020-01-28' at time zone 'jst')
    , (12, '8maki', '2020-01-30' at time zone 'jst')
    , (13, '24maki', '2020-02-01' at time zone 'jst')
    , (14, '31maki', '2020-02-01' at time zone 'jst')
    , (15, '35maki', '2020-02-02' at time zone 'jst')
    , (16, '17maki', '2020-02-03' at time zone 'jst')
    , (17, '20maki', '2020-02-06' at time zone 'jst')
    , (18, '9maki', '2020-02-07' at time zone 'jst')
    , (19, '27maki', '2020-02-07' at time zone 'jst')
    , (20, '30maki', '2020-02-08' at time zone 'jst')
    , (21, '22maki', '2020-02-13' at time zone 'jst')
    , (22, '29maki', '2020-02-13' at time zone 'jst')
    , (23, '11maki', '2020-02-14' at time zone 'jst')
    , (24, '26maki', '2020-02-14' at time zone 'jst')
    , (25, '18maki', '2020-02-15' at time zone 'jst')
    , (26, '19maki', '2020-02-17' at time zone 'jst')
    , (27, '15maki', '2020-02-18' at time zone 'jst')
    , (28, '28maki', '2020-02-24' at time zone 'jst')
    , (29, '10maki', '2020-02-25' at time zone 'jst')
    , (30, '10maki', '2020-03-01' at time zone 'jst')
)

, item(id, category) as (
  values
    (1, 'A')
    , (2, 'B')
    , (3, 'C')
)

, purchase(id, user_id, item_id, amount, purchased_at) as (
  values
    (1, 15, 1, 1800, '2020-02-01' at time zone 'jst')
    , (2, 5, 1, 900, '2020-02-03' at time zone 'jst')
    , (3, 1, 1, 1300, '2020-02-04' at time zone 'jst')
    , (4, 20, 3, 2200, '2020-02-06' at time zone 'jst')
    , (5, 12, 1, 2300, '2020-02-10' at time zone 'jst')
    , (6, 11, 1, 1100, '2020-02-12' at time zone 'jst')
    , (7, 20, 2, 2100, '2020-02-14' at time zone 'jst')
    , (8, 10, 2, 3300, '2020-02-17' at time zone 'jst')
    , (9, 19, 3, 3700, '2020-02-18' at time zone 'jst')
    , (10, 9, 1, 3800, '2020-02-20' at time zone 'jst')
    , (11, 15, 1, 1300, '2020-02-21' at time zone 'jst')
    , (12, 1, 1, 2100, '2020-02-22' at time zone 'jst')
    , (13, 5, 2, 2800, '2020-02-25' at time zone 'jst')
    , (14, 11, 1, 3600, '2020-03-05' at time zone 'jst')
    , (15, 1, 3, 3400, '2020-03-07' at time zone 'jst')
    , (16, 2, 3, 2300, '2020-03-10' at time zone 'jst')
    , (17, 15, 2, 3300, '2020-03-10' at time zone 'jst')
    , (18, 17, 3, 2400, '2020-03-12' at time zone 'jst')
    , (19, 10, 1, 2600, '2020-03-12' at time zone 'jst')
    , (20, 19, 3, 300, '2020-03-16' at time zone 'jst')
    , (21, 4, 3, 2900, '2020-03-16' at time zone 'jst')
    , (22, 14, 3, 2000, '2020-03-16' at time zone 'jst')
    , (23, 9, 3, 2600, '2020-03-18' at time zone 'jst')
    , (24, 10, 3, 2800, '2020-03-19' at time zone 'jst')
    , (25, 3, 1, 2000, '2020-03-22' at time zone 'jst')
    , (26, 2, 2, 2200, '2020-03-22' at time zone 'jst')
    , (27, 19, 1, 1700, '2020-03-22' at time zone 'jst')
    , (28, 6, 1, 3200, '2020-03-24' at time zone 'jst')
    , (29, 6, 1, 2000, '2020-03-25' at time zone 'jst')
    , (30, 17, 3, 3100, '2020-03-25' at time zone 'jst')
    , (31, 1, 1, 600, '2020-03-26' at time zone 'jst')
    , (32, 12, 3, 1800, '2020-03-28' at time zone 'jst')
    , (33, 6, 1, 3400, '2020-03-29' at time zone 'jst')
    , (34, 13, 2, 2500, '2020-03-29' at time zone 'jst')
    , (35, 19, 3, 1800, '2020-03-30' at time zone 'jst')
)

, args(from_dt, to_dt) as (
  select '2020-01-01 00:00:00' at time zone 'jst', '2020-02-01 00:00:00' at time zone 'jst'
)

, target_user as (
  select
    u.id
    , u.name
    , u.registered_at
  from shop_user u
  cross join args a
  where u.registered_at >= a.from_dt
  and u.registered_at < a.to_dt
)

, mt_purchase_amt as (
  select
    tu.id
    , tu.name
    , sum(t.amount) amt
  from target_user tu
  left join (
    select
      p.user_id
      , p.amount
      , p.item_id
    from purchase p
    cross join args a
    where p.purchased_at >= a.from_dt + '1month'
    and p.purchased_at < a.to_dt + '1month'
  ) t
  on t.user_id = tu.id
  group by
    tu.id
    , tu.name
)

, mt_purchase_cnt as (
  select
    tu.id
    , tu.name
    , count(t.purchase_id) cnt
  from target_user tu
  left join (
    select
      p.user_id
      , p.id as purchase_id
      , p.amount
      , p.item_id
    from purchase p
    cross join args a
    where p.purchased_at >= a.from_dt + '1month'
    and p.purchased_at < a.to_dt + '1month'
  ) t
  on t.user_id = tu.id
  group by
    tu.id
    , tu.name
)

, mt_user_segment as (
  select
    a.id
    , case
        when a.amt is null then '01_0k'
        when a.amt > 0 and a.amt <= 2000 then '02_1k-2k'
        when a.amt > 2000 and a.amt <= 3000 then '03_2k-3k'
        when a.amt >= 3000  then '04_3k-'
      end amt_type
    , case
        when c.cnt = 0 then '01_0'
        when c.cnt = 1 then '02_1'
        when c.cnt = 2 then '03_2'
        when c.cnt >= 3 then '04_3-'
      end cnt_type
  from mt_purchase_amt a
  inner join mt_purchase_cnt c
  on a.id = c.id
)

select
  s.amt_type
  , s.cnt_type
  , count(*) uu
from mt_user_segment s
group by
  s.amt_type
  , s.cnt_type