/
customers_proc_yoy.sql
110 lines (102 loc) · 2.62 KB
/
customers_proc_yoy.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
WITH customers AS (
SELECT
store,
customer_id,
order_date,
unix_date(order_date) unix_order_date,
first_order_date,
unix_date(first_order_date) first_order_unix_date,
first_order_revenue,
first_order_channel,
first_order_platform,
channel,
platform,
url,
campaign,
quantity,
revenue,
orders
FROM {{ref('agg_transactions')}}
),
daterange AS (
SELECT * FROM {{ref('monthend_dates')}}
)
SELECT
store,
period,
customer_id,
date,
window_end_unix_date,
window_start_unix_date,
first_order_unix_date,
first_order_channel,
first_order_platform,
recency_days,
frequency,
quantity,
revenue,
PERCENTILE_CONT(revenue, 0.90) OVER w1 AS revenue_90pct,
PERCENTILE_CONT(revenue, 0.10) OVER w1 AS revenue_10pct
FROM (
SELECT
store,
period,
customer_id,
date,
window_end_unix_date,
window_start_unix_date,
first_order_unix_date,
window_end_unix_date - unix_date(recent_order) recency_days,
first_order_channel,
first_order_platform,
quantity,
revenue,
frequency
FROM
(
SELECT
store,
'Rolling Year' as period,
customer_id,
date_in_range date,
unix_date_in_range,
unix_date_in_range window_end_unix_date,
unix_date_in_range - 365 window_start_unix_date,
first_order_unix_date,
first_order_channel,
first_order_platform,
max(order_date) recent_order,
sum(quantity) as quantity,
sum(revenue) as revenue,
sum(orders) as frequency
FROM daterange
JOIN customers
ON customers.unix_order_date > ( daterange.unix_date_in_range - 365 )
AND customers.unix_order_date <= daterange.unix_date_in_range
GROUP BY store, customer_id, date, unix_date_in_range, window_end_unix_date,
window_start_unix_date, first_order_unix_date, first_order_channel, first_order_platform
UNION ALL
SELECT
store,
'Rolling Previous Year' as period,
customer_id,
date_in_range date,
unix_date_in_range,
unix_date_in_range - 365 window_end_unix_date,
unix_date_in_range - 730 window_start_unix_date,
first_order_unix_date,
first_order_channel,
first_order_platform,
max(order_date) recent_order,
sum(quantity) as quantity,
sum(revenue) as revenue,
sum(orders) as frequency
FROM daterange
JOIN customers
ON customers.unix_order_date > ( daterange.unix_date_in_range - 730 )
AND customers.unix_order_date <= ( daterange.unix_date_in_range - 365 )
GROUP BY store, customer_id, date, unix_date_in_range, window_end_unix_date,
window_start_unix_date, first_order_unix_date, first_order_channel, first_order_platform
)
)
WINDOW w1 as (PARTITION BY store, period, date)