Skip to content

Instantly share code, notes, and snippets.

@achiku

achiku/segment-sql-v1.sql

Last active Jan 30, 2021
Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment