· 8 years ago · Jan 17, 2018, 07:26 AM
1DROP TABLE IF EXISTS messages;
2DROP TABLE IF EXISTS chats;
3
4CREATE TABLE chats (
5 client integer NOT NULL,
6 provider integer NOT NULL,
7 PRIMARY KEY (client, provider)
8);
9
10CREATE TABLE messages (
11 id serial PRIMARY KEY,
12 client integer NOT NULL,
13 provider integer NOT NULL,
14 sender integer NOT NULL CHECK(sender = client OR sender = provider),
15 created_at timestamp with time zone NOT NULL,
16 FOREIGN KEY (client, provider) REFERENCES chats(client, provider) ON UPDATE CASCADE ON DELETE CASCADE
17);
18
19INSERT INTO chats VALUES (1,2), (3,2), (1,3);
20INSERT INTO messages (client, provider, sender, created_at) VALUES
21 (1, 2, 1, '2017-06-13 17:00:00+0'),
22 (1, 2, 1, '2017-06-13 17:00:10+0'),
23 (1, 2, 1, '2017-06-13 17:01:00+0'),
24 (1, 2, 2, '2017-06-13 17:10:00+0'),
25 (1, 2, 2, '2017-06-13 17:10:10+0'),
26 (1, 2, 2, '2017-06-13 17:10:20+0'),
27 (1, 2, 1, '2017-06-13 17:11:00+0'),
28 (1, 2, 2, '2017-06-13 17:11:10+0');
29
30INSERT INTO messages (client, provider, sender, created_at) VALUES
31 (3, 2, 2, '2017-06-13 17:05:00+0'),
32 (3, 2, 2, '2017-06-13 17:05:10+0'),
33 (3, 2, 2, '2017-06-13 17:05:20+0'),
34 (3, 2, 3, '2017-06-13 17:12:00+0'),
35 (3, 2, 2, '2017-06-13 17:12:10+0'),
36 (3, 2, 2, '2017-06-13 17:12:15+0'),
37 (3, 2, 3, '2017-06-13 17:12:30+0'),
38 (3, 2, 3, '2017-06-13 17:14:00+0');
39
40INSERT INTO messages (client, provider, sender, created_at) VALUES
41 (1, 3, 1, '2017-06-13 17:05:00+0'),
42 (1, 3, 1, '2017-06-13 17:05:10+0');
43
44postgres=# table messages order by client, provider, created_at;
45 id | client | provider | sender | created_at
46----+--------+----------+--------+------------------------
47 1 | 1 | 2 | 1 | 2017-06-13 13:00:00-04
48 2 | 1 | 2 | 1 | 2017-06-13 13:00:10-04
49 3 | 1 | 2 | 1 | 2017-06-13 13:01:00-04
50--
51 4 | 1 | 2 | 2 | 2017-06-13 13:10:00-04
52 5 | 1 | 2 | 2 | 2017-06-13 13:10:10-04
53 6 | 1 | 2 | 2 | 2017-06-13 13:10:20-04
54--
55 7 | 1 | 2 | 1 | 2017-06-13 13:11:00-04
56--
57 8 | 1 | 2 | 2 | 2017-06-13 13:11:10-04
58--
59 17 | 1 | 3 | 1 | 2017-06-13 13:05:00-04
60 18 | 1 | 3 | 1 | 2017-06-13 13:05:10-04
61--
62 9 | 3 | 2 | 2 | 2017-06-13 13:05:00-04
63 10 | 3 | 2 | 2 | 2017-06-13 13:05:10-04
64 11 | 3 | 2 | 2 | 2017-06-13 13:05:20-04
65--
66 12 | 3 | 2 | 3 | 2017-06-13 13:12:00-04
67--
68 13 | 3 | 2 | 2 | 2017-06-13 13:12:10-04
69 14 | 3 | 2 | 2 | 2017-06-13 13:12:15-04
70--
71 15 | 3 | 2 | 3 | 2017-06-13 13:12:30-04
72 16 | 3 | 2 | 3 | 2017-06-13 13:14:00-04
73(18 rows)
74
75client | provider | sender | first_created_at
76--------+----------+--------+------------------------
77 1 | 2 | 1 | 2017-06-13 13:00:00-04
78 1 | 2 | 2 | 2017-06-13 13:10:00-04
79 1 | 2 | 1 | 2017-06-13 13:11:00-04
80 1 | 2 | 2 | 2017-06-13 13:11:10-04
81 1 | 3 | 1 | 2017-06-13 13:05:00-04
82 3 | 2 | 2 | 2017-06-13 13:05:00-04
83 3 | 2 | 3 | 2017-06-13 13:12:00-04
84 3 | 2 | 2 | 2017-06-13 13:12:10-04
85 3 | 2 | 3 | 2017-06-13 13:12:30-04
86
87postgres=# SELECT * FROM messages WHERE (client, provider) = (1,2) ORDER BY created_at;
88 id | client | provider | sender | created_at
89----+--------+----------+--------+------------------------
90 1 | 1 | 2 | 1 | 2017-06-13 13:00:00-04
91 2 | 1 | 2 | 1 | 2017-06-13 13:00:10-04
92 3 | 1 | 2 | 1 | 2017-06-13 13:01:00-04
93--
94 4 | 1 | 2 | 2 | 2017-06-13 13:10:00-04
95 5 | 1 | 2 | 2 | 2017-06-13 13:10:10-04
96 6 | 1 | 2 | 2 | 2017-06-13 13:10:20-04
97--
98 7 | 1 | 2 | 1 | 2017-06-13 13:11:00-04
99--
100 8 | 1 | 2 | 2 | 2017-06-13 13:11:10-04
101(8 rows)
102
103postgres=# SELECT * FROM messages WHERE (client, provider) = (3,2) ORDER BY created_at;
104 id | client | provider | sender | created_at
105----+--------+----------+--------+------------------------
106 9 | 3 | 2 | 2 | 2017-06-13 13:05:00-04
107 10 | 3 | 2 | 2 | 2017-06-13 13:05:10-04
108 11 | 3 | 2 | 2 | 2017-06-13 13:05:20-04
109--
110 12 | 3 | 2 | 3 | 2017-06-13 13:12:00-04
111--
112 13 | 3 | 2 | 2 | 2017-06-13 13:12:10-04
113 14 | 3 | 2 | 2 | 2017-06-13 13:12:15-04
114--
115 15 | 3 | 2 | 3 | 2017-06-13 13:12:30-04
116 16 | 3 | 2 | 3 | 2017-06-13 13:14:00-04
117(8 rows)
118
119postgres=# SELECT * FROM messages WHERE (client, provider) = (1,3) ORDER BY created_at;
120 id | client | provider | sender | created_at
121----+--------+----------+--------+------------------------
122 17 | 1 | 3 | 1 | 2017-06-13 13:05:00-04
123 18 | 1 | 3 | 1 | 2017-06-13 13:05:10-04
124(2 rows)
125
126SELECT client, provider, sender, min(created_at) AS first_created_at
127FROM (
128 SELECT count(CASE WHEN is_reset THEN 1 END) OVER (ORDER BY created_at) AS grp,
129 client,
130 provider,
131 sender,
132 created_at
133 FROM (
134 SELECT
135 (client,provider,sender) <> lag( (client,provider,sender) ) OVER (ORDER BY created_at) AS is_reset,
136 client,
137 provider,
138 sender,
139 created_at
140 FROM messages
141 ) AS t1
142) AS t2
143GROUP BY grp, client, provider, sender
144HAVING count(*) > 1;
145
146SELECT
147 (client,provider,sender) <> lag( (client,provider,sender) ) OVER (ORDER BY created_at) AS is_reset,
148 client,
149 provider,
150 sender,
151 created_at
152FROM messages
153
154 is_reset | client | provider | sender | created_at
155----------+--------+----------+--------+------------------------
156 | 1 | 2 | 1 | 2017-06-13 12:00:00-05
157 f | 1 | 2 | 1 | 2017-06-13 12:00:10-05
158 f | 1 | 2 | 1 | 2017-06-13 12:01:00-05
159 t | 3 | 2 | 2 | 2017-06-13 12:05:00-05
160 t | 1 | 3 | 1 | 2017-06-13 12:05:00-05
161 f | 1 | 3 | 1 | 2017-06-13 12:05:10-05
162 t | 3 | 2 | 2 | 2017-06-13 12:05:10-05
163 f | 3 | 2 | 2 | 2017-06-13 12:05:20-05
164 t | 1 | 2 | 2 | 2017-06-13 12:10:00-05
165 f | 1 | 2 | 2 | 2017-06-13 12:10:10-05
166 f | 1 | 2 | 2 | 2017-06-13 12:10:20-05
167 t | 1 | 2 | 1 | 2017-06-13 12:11:00-05
168 t | 1 | 2 | 2 | 2017-06-13 12:11:10-05
169 t | 3 | 2 | 3 | 2017-06-13 12:12:00-05
170 t | 3 | 2 | 2 | 2017-06-13 12:12:10-05
171 f | 3 | 2 | 2 | 2017-06-13 12:12:15-05
172 t | 3 | 2 | 3 | 2017-06-13 12:12:30-05
173 f | 3 | 2 | 3 | 2017-06-13 12:14:00-05
174(18 rows)
175
176SELECT count(CASE WHEN is_reset THEN 1 END) OVER (ORDER BY created_at) AS grp,
177 client,
178 provider,
179 sender,
180 created_at
181FROM (
182 SELECT
183 (client,provider,sender) <> lag( (client,provider,sender) ) OVER (ORDER BY created_at) AS is_reset,
184 client,
185 provider,
186 sender,
187 created_at
188 FROM messages
189) AS t1;
190 grp | client | provider | sender | created_at
191-----+--------+----------+--------+------------------------
192 0 | 1 | 2 | 1 | 2017-06-13 12:00:00-05
193 0 | 1 | 2 | 1 | 2017-06-13 12:00:10-05
194 0 | 1 | 2 | 1 | 2017-06-13 12:01:00-05
195 2 | 3 | 2 | 2 | 2017-06-13 12:05:00-05
196 2 | 1 | 3 | 1 | 2017-06-13 12:05:00-05
197 3 | 1 | 3 | 1 | 2017-06-13 12:05:10-05
198 3 | 3 | 2 | 2 | 2017-06-13 12:05:10-05
199 3 | 3 | 2 | 2 | 2017-06-13 12:05:20-05
200 4 | 1 | 2 | 2 | 2017-06-13 12:10:00-05
201 4 | 1 | 2 | 2 | 2017-06-13 12:10:10-05
202 4 | 1 | 2 | 2 | 2017-06-13 12:10:20-05
203 5 | 1 | 2 | 1 | 2017-06-13 12:11:00-05
204 6 | 1 | 2 | 2 | 2017-06-13 12:11:10-05
205 7 | 3 | 2 | 3 | 2017-06-13 12:12:00-05
206 8 | 3 | 2 | 2 | 2017-06-13 12:12:10-05
207 8 | 3 | 2 | 2 | 2017-06-13 12:12:15-05
208 9 | 3 | 2 | 3 | 2017-06-13 12:12:30-05
209 9 | 3 | 2 | 3 | 2017-06-13 12:14:00-05
210(18 rows)
211
212postgres=# SELECT
213 *,
214 lag((client, provider, sender)) OVER chats_window
215FROM messages
216WINDOW chats_window AS (
217 PARTITION BY client, provider
218 ORDER BY created_at
219);
220 id | client | provider | sender | created_at | lag
221----+--------+----------+--------+------------------------+---------
222 1 | 1 | 2 | 1 | 2017-06-13 13:00:00-04 |
223 2 | 1 | 2 | 1 | 2017-06-13 13:00:10-04 | (1,2,1)
224 3 | 1 | 2 | 1 | 2017-06-13 13:01:00-04 | (1,2,1)
225 4 | 1 | 2 | 2 | 2017-06-13 13:10:00-04 | (1,2,1)
226 5 | 1 | 2 | 2 | 2017-06-13 13:10:10-04 | (1,2,2)
227 6 | 1 | 2 | 2 | 2017-06-13 13:10:20-04 | (1,2,2)
228 7 | 1 | 2 | 1 | 2017-06-13 13:11:00-04 | (1,2,2)
229 8 | 1 | 2 | 2 | 2017-06-13 13:11:10-04 | (1,2,1)
230 17 | 1 | 3 | 1 | 2017-06-13 13:05:00-04 |
231 18 | 1 | 3 | 1 | 2017-06-13 13:05:10-04 | (1,3,1)
232 9 | 3 | 2 | 2 | 2017-06-13 13:00:30-04 |
233 10 | 3 | 2 | 2 | 2017-06-13 13:00:50-04 | (3,2,2)
234 11 | 3 | 2 | 2 | 2017-06-13 13:05:20-04 | (3,2,2)
235 12 | 3 | 2 | 3 | 2017-06-13 13:12:00-04 | (3,2,2)
236 13 | 3 | 2 | 2 | 2017-06-13 13:12:10-04 | (3,2,3)
237 14 | 3 | 2 | 2 | 2017-06-13 13:12:15-04 | (3,2,2)
238 15 | 3 | 2 | 3 | 2017-06-13 13:12:30-04 | (3,2,2)
239 16 | 3 | 2 | 3 | 2017-06-13 13:14:00-04 | (3,2,3)
240(18 rows)
241
242postgres=# SELECT
243 client, provider, sender, created_at,
244 coalesce(lag((client, provider, sender)) OVER chats_window <> (client, provider, sender), true) AS thread_starter
245FROM messages
246WINDOW chats_window AS (
247 PARTITION BY client, provider
248 ORDER BY created_at
249);
250 client | provider | sender | created_at | thread_starter
251--------+----------+--------+------------------------+----------------
252 1 | 2 | 1 | 2017-06-13 13:00:00-04 | t
253 1 | 2 | 1 | 2017-06-13 13:00:10-04 | f
254 1 | 2 | 1 | 2017-06-13 13:01:00-04 | f
255 1 | 2 | 2 | 2017-06-13 13:10:00-04 | t
256 1 | 2 | 2 | 2017-06-13 13:10:10-04 | f
257 1 | 2 | 2 | 2017-06-13 13:10:20-04 | f
258 1 | 2 | 1 | 2017-06-13 13:11:00-04 | t
259 1 | 2 | 2 | 2017-06-13 13:11:10-04 | t
260 1 | 3 | 1 | 2017-06-13 13:05:00-04 | t
261 1 | 3 | 1 | 2017-06-13 13:05:10-04 | f
262 3 | 2 | 2 | 2017-06-13 13:00:30-04 | t
263 3 | 2 | 2 | 2017-06-13 13:00:50-04 | f
264 3 | 2 | 2 | 2017-06-13 13:05:20-04 | f
265 3 | 2 | 3 | 2017-06-13 13:12:00-04 | t
266 3 | 2 | 2 | 2017-06-13 13:12:10-04 | t
267 3 | 2 | 2 | 2017-06-13 13:12:15-04 | f
268 3 | 2 | 3 | 2017-06-13 13:12:30-04 | t
269 3 | 2 | 3 | 2017-06-13 13:14:00-04 | f
270(18 rows)
271
272postgres=# WITH thread_starts AS (
273 SELECT
274 client, provider, sender, created_at,
275 coalesce(lag((client, provider, sender)) OVER chats_window <> (client, provider, sender), true) AS thread_starter
276 FROM messages
277 WINDOW chats_window AS (
278 PARTITION BY client, provider
279 ORDER BY created_at
280 )
281)
282SELECT
283 client, provider, sender, created_at,
284 lead(created_at) OVER chats_window AS responded_at,
285 count(*) OVER (PARTITION BY client, provider) chat_threads_count
286FROM thread_starts
287WHERE thread_starter
288WINDOW chats_window AS (
289 PARTITION BY client, provider
290 ORDER BY created_at
291);
292 client | provider | sender | created_at | responded_at | chat_threads_count
293--------+----------+--------+------------------------+------------------------+---------------
294 1 | 2 | 1 | 2017-06-13 13:00:00-04 | 2017-06-13 13:10:00-04 | 4
295 1 | 2 | 2 | 2017-06-13 13:10:00-04 | 2017-06-13 13:11:00-04 | 4
296 1 | 2 | 1 | 2017-06-13 13:11:00-04 | 2017-06-13 13:11:10-04 | 4
297 1 | 2 | 2 | 2017-06-13 13:11:10-04 | | 4
298 1 | 3 | 1 | 2017-06-13 13:05:00-04 | | 1
299 3 | 2 | 2 | 2017-06-13 13:00:30-04 | 2017-06-13 13:12:00-04 | 4
300 3 | 2 | 3 | 2017-06-13 13:12:00-04 | 2017-06-13 13:12:10-04 | 4
301 3 | 2 | 2 | 2017-06-13 13:12:10-04 | 2017-06-13 13:12:30-04 | 4
302 3 | 2 | 3 | 2017-06-13 13:12:30-04 | | 4
303 (9 rows)
304
305postgres=# WITH thread_starts AS (
306 SELECT
307 client, provider, sender, created_at,
308 coalesce(lag((client, provider, sender)) OVER chats_window <> (client, provider, sender), true) AS thread_starter
309 FROM messages
310 WINDOW chats_window AS (
311 PARTITION BY client, provider
312 ORDER BY created_at
313 )
314 ), response_intervals AS (
315 SELECT
316 client, provider, sender, created_at,
317 lead(created_at) OVER chats_window AS responded_at,
318 count(*) OVER (PARTITION BY client, provider) threads_count
319 FROM thread_starts
320 WHERE thread_starter
321 WINDOW chats_window AS (
322 PARTITION BY client, provider
323 ORDER BY created_at
324 )
325 )
326SELECT
327 provider,
328 avg(coalesce(responded_at, CURRENT_TIMESTAMP) - created_at) AS response_interval_avg
329FROM response_intervals
330WHERE sender = client AND (responded_at IS NOT NULL OR threads_count = 1)
331GROUP BY provider;
332 provider | response_interval_avg
333----------+-----------------------
334 2 | 00:03:26.666667
335 3 | 23:19:46.228611
336(2 rows)