forked from jamil-said/code-samples
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathrepeatGroupTime.sql
More file actions
executable file
·88 lines (71 loc) · 2.28 KB
/
repeatGroupTime.sql
File metadata and controls
executable file
·88 lines (71 loc) · 2.28 KB
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
/* repeatGroupTime -- MySQL
Given a table "grouptimes" as following:
CREATE TABLE grouptimes (
group_number integer not null,
value integer not null,
time timestamp not null,
unique(group_number, time)
);
create a SQL query that, for each group_number that appears more than once,
returns the difference between the latest value (most recent in terms
of time) and the second latest value. The table should be ordered by
group_number (ascending order).
For example, given the following data:
group_number | value | time
--------------+-------+---------------------
4 | 8 | 2017-08-12 11:32:00
5 | -2 | 2017-08-12 11:39:58
4 | 2 | 2017-08-12 13:37:35
4 | 12 | 2017-08-12 09:50:23
6 | 11 | 2017-08-12 08:12:51
6 | -3 | 2017-08-12 09:11:00
your query should return the following result:
group_number | value
--------------+-------
4 | -6
6 | -14
For the group_number 4, the latest value is 2 and the second latest value
is 8, so the difference between them is -6.
*/
/* Create database
mysql -u root -p
CREATE DATABASE repeatgroup;
USE repeatgroup;
*/
/* Create example table and populate it
CREATE TABLE grouptimes (
group_number integer not null,
value integer not null,
time timestamp not null,
unique(group_number, time)
);
INSERT INTO grouptimes (group_number, value, time) VALUES
(4, 8, '2017-08-12 11:32:00'),
(5, -2, '2017-08-12 11:39:58'),
(4, 2, '2017-08-12 13:37:35'),
(4, 12, '2017-08-12 9:50:23'),
(6, 11, '2017-08-12 8:12:51'),
(6, -3, '2017-08-12 9:11:00');
*/
SELECT gr1.group_number, (gr1.value - gr2.value) AS value
FROM grouptimes gr1
JOIN grouptimes gr2
ON gr1.group_number = gr2.group_number
AND gr1.time = (SELECT tmp1.time
FROM grouptimes tmp1
WHERE tmp1.group_number=gr1.group_number
ORDER BY time DESC
LIMIT 1)
AND gr2.time = (SELECT tmp2.time
FROM grouptimes tmp2
WHERE tmp2.group_number=gr2.group_number
ORDER BY time DESC
LIMIT 1
OFFSET 1)
ORDER BY group_number;
/* expected result
group_number | value
--------------+-------
4 | -6
6 | -14
*/