-
Notifications
You must be signed in to change notification settings - Fork 1
/
109_Identify returning active users (Finding User Purchases-stratascratch).sql
145 lines (138 loc) · 4.71 KB
/
109_Identify returning active users (Finding User Purchases-stratascratch).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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
--Finding User Purchases (find returning active users)
/*
Write a query that'll identify returning active users.
A returning active user is a user that has made a second purchase within 7 days of any other of their purchases.
Output a list of user_ids of these returning active users.
*/
use int_ques;
/*
create table flipkart_transactions(
id int,
user_id int,
item varchar(50),
created_at date,
revenue int
)
*/
/*
insert into flipkart_transactions(id, user_id, item, created_at, revenue)
values(1, 109 ,'milk','2020-03-03',123),(
2, 139 ,'biscuit','2020-03-18',421),(
3, 120 ,'milk','2020-03-18',176),(
4, 108 ,'banana','2020-03-18',862),(
5, 130 ,'milk','2020-03-28',333),(
6, 103 ,'bread','2020-03-29',862),(
7, 122 ,'banana','2020-03-07',952),(
8, 125 ,'bread','2020-03-13',317),(
9, 139 ,'bread','2020-03-30',929),(
10, 141 ,'banana','2020-03-17',812),(
11, 116 ,'bread','2020-03-31',226),(
12, 128 ,'bread','2020-03-04',112),(
13, 146 ,'biscuit','2020-03-04',362),(
14, 119 ,'banana','2020-03-28',127),(
15, 142 ,'bread','2020-03-09',503),(
16, 122 ,'bread','2020-03-06',593),(
17, 128 ,'biscuit','2020-03-24',160),(
18, 112 ,'banana','2020-03-24',262),(
19, 149 ,'banana','2020-03-29',382),(
20, 100 ,'banana','2020-03-18',599),(
21, 130 ,'milk','2020-03-16',604),(
22, 103 ,'milk','2020-03-31',290),(
23, 112 ,'banana','2020-03-23',523),(
24, 102 ,'bread','2020-03-25',325),(
25, 120 ,'biscuit','2020-03-21',858),(
26, 109 ,'bread','2020-03-22',432),(
27, 101 ,'milk','2020-03-01',449),(
28, 138 ,'milk','2020-03-19',961),(
29, 100 ,'milk','2020-03-29',410),(
30, 129 ,'milk','2020-03-02',771),(
31, 123 ,'milk','2020-03-31',434),(
32, 104 ,'biscuit','2020-03-31',957),(
33, 110 ,'bread','2020-03-13',210),(
34, 143 ,'bread','2020-03-27',870),(
35, 130 ,'milk','2020-03-12',176),(
36, 128 ,'milk','2020-03-28',498),(
37, 133 ,'banana','2020-03-21',837),(
38, 150 ,'banana','2020-03-20',927),(
39, 120 ,'milk','2020-03-27',793),(
40, 109 ,'bread','2020-03-02',362),(
41, 110 ,'bread','2020-03-13',262),(
42, 140 ,'milk','2020-03-09',468),(
43, 112 ,'banana','2020-03-04',381),(
44, 117 ,'biscuit','2020-03-19',831),(
45, 137 ,'banana','2020-03-23',490),(
46, 130 ,'bread','2020-03-09',149),(
47, 133 ,'bread','2020-03-08',658),(
48, 143 ,'milk','2020-03-11',317),(
49, 111 ,'biscuit','2020-03-23',204),(
50, 150 ,'banana','2020-03-04',299),(
51, 131 ,'bread','2020-03-10',155),(
52, 140 ,'biscuit','2020-03-17',810),(
53, 147 ,'banana','2020-03-22',702),(
54, 119 ,'biscuit','2020-03-15',355),(
55, 116 ,'milk','2020-03-12',468),(
56, 141 ,'milk','2020-03-14',254),(
57, 143 ,'bread','2020-03-16',647),(
58, 105 ,'bread','2020-03-21',562),(
59, 149 ,'biscuit','2020-03-11',827),(
60, 117 ,'banana','2020-03-22',249),(
61, 150 ,'banana','2020-03-21',450),(
62, 134 ,'bread','2020-03-08',981),(
63, 133 ,'banana','2020-03-26',353),(
64, 127 ,'milk','2020-03-27',300),(
65, 101 ,'milk','2020-03-26',740),(
66, 137 ,'biscuit','2020-03-12',473),(
67, 113 ,'biscuit','2020-03-21',278),(
68, 141 ,'bread','2020-03-21',118),(
69, 112 ,'biscuit','2020-03-14',334),(
70, 118 ,'milk','2020-03-30',603),(
71, 111 ,'milk','2020-03-19',205),(
72, 146 ,'biscuit','2020-03-13',599),(
73, 148 ,'banana','2020-03-14',530),(
74, 100 ,'banana','2020-03-13',175),(
75, 105 ,'banana','2020-03-05',815),(
76, 129 ,'milk','2020-03-02',489),(
77, 121 ,'milk','2020-03-16',476),(
78, 117 ,'bread','2020-03-11',270),(
79, 133 ,'milk','2020-03-12',446),(
80, 124 ,'bread','2020-03-31',937),(
81, 145 ,'bread','2020-03-07',821),(
82, 105 ,'banana','2020-03-09',972),(
83, 131 ,'milk','2020-03-09',808),(
84, 114 ,'biscuit','2020-03-31',202),(
85, 120 ,'milk','2020-03-06',898),(
86, 130 ,'milk','2020-03-06',581),(
87, 141 ,'biscuit','2020-03-11',749),(
88, 147 ,'bread','2020-03-14',262),(
89, 118 ,'milk','2020-03-15',735),(
90, 136 ,'biscuit','2020-03-22',410),(
91, 132 ,'bread','2020-03-06',161),(
92, 137 ,'biscuit','2020-03-31',427),(
93, 107 ,'bread','2020-03-01',701),(
94, 111 ,'biscuit','2020-03-18',218),(
95, 100 ,'bread','2020-03-07',410),(
96, 106 ,'milk','2020-03-21',379),(
97, 114 ,'banana','2020-03-25',705),(
98, 110 ,'bread','2020-03-27',225),(
99, 130 ,'milk','2020-03-16',494),(
100, 117 ,'bread','2020-03-10',209)
*/
with t1 as
(
select ft.*,
lag(created_at) over(partition by user_id order by created_at asc) as previous_purchase_date
from flipkart_transactions ft
--order by user_id, created_at asc
),
t2 as
(
select t1.*,
datediff(day,t1.previous_purchase_date,t1.created_at) as date_diff
from t1
where datediff(day,t1.previous_purchase_date,t1.created_at) <= 7
--order by t1.user_id, created_at asc
)
select user_id
from t2
group by user_id
order by user_id asc