API tools faq
paste
Guest User

Untitled

a guest
Jan 17th, 2018
301
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.82 KB | None | 0 0
raw download clone embed print report
  1. DROP TABLE IF EXISTS messages;
  2. DROP TABLE IF EXISTS chats;
  3.  
  4. CREATE TABLE chats (
  5. client integer NOT NULL,
  6. provider integer NOT NULL,
  7. PRIMARY KEY (client, provider)
  8. );
  9.  
  10. CREATE 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.  
  19. INSERT INTO chats VALUES (1,2), (3,2), (1,3);
  20. INSERT 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.  
  30. INSERT 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.  
  40. INSERT 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.  
  44. postgres=# 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.  
  75. client | 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.  
  87. postgres=# 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.  
  103. postgres=# 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.  
  119. postgres=# 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.  
  126. SELECT client, provider, sender, min(created_at) AS first_created_at
  127. FROM (
  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
  143. GROUP BY grp, client, provider, sender
  144. HAVING count(*) > 1;
  145.  
  146. SELECT
  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
  152. FROM 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.  
  176. SELECT count(CASE WHEN is_reset THEN 1 END) OVER (ORDER BY created_at) AS grp,
  177. client,
  178. provider,
  179. sender,
  180. created_at
  181. FROM (
  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.  
  212. postgres=# SELECT
  213. *,
  214. lag((client, provider, sender)) OVER chats_window
  215. FROM messages
  216. WINDOW 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.  
  242. postgres=# SELECT
  243. client, provider, sender, created_at,
  244. coalesce(lag((client, provider, sender)) OVER chats_window <> (client, provider, sender), true) AS thread_starter
  245. FROM messages
  246. WINDOW 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.  
  272. postgres=# 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. )
  282. SELECT
  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
  286. FROM thread_starts
  287. WHERE thread_starter
  288. WINDOW 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.  
  305. postgres=# 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. )
  326. SELECT
  327. provider,
  328. avg(coalesce(responded_at, CURRENT_TIMESTAMP) - created_at) AS response_interval_avg
  329. FROM response_intervals
  330. WHERE sender = client AND (responded_at IS NOT NULL OR threads_count = 1)
  331. GROUP BY provider;
  332. provider | response_interval_avg
  333. ----------+-----------------------
  334. 2 | 00:03:26.666667
  335. 3 | 23:19:46.228611
  336. (2 rows)
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!