Tasks Details
easy
Compute the difference between the latest and the second latest value for each event type.
Task Score
100%
Correctness
100%
Performance
Not assessed
Given a table events with the following structure:
create table events ( event_type integer not null, value integer not null, time timestamp not null, unique(event_type, time) );write an SQL query that, for each event_type that has been registered more than once, returns the difference between the latest (i.e. the most recent in terms of time) and the second latest value. The table should be ordered by event_type (in ascending order).
For example, given the following data:
event_type | value | time ------------+------------+-------------------- 2 | 5 | 2015-05-09 12:42:00 4 | -42 | 2015-05-09 13:19:57 2 | 2 | 2015-05-09 14:48:30 2 | 7 | 2015-05-09 12:54:39 3 | 16 | 2015-05-09 13:19:57 3 | 20 | 2015-05-09 15:01:09your query should return the following rowset:
event_type | value ------------+----------- 2 | -5 3 | 4For the event_type 2, the latest value is 2 and the second latest value is 7, so the difference between them is −5.
The names of the columns in the rowset don't matter, but their order does.
Copyright 2009–2025 by Codility Limited. All Rights Reserved. Unauthorized copying, publication or disclosure prohibited.
Solution
Programming language used SQL (PostgreSQL)
Time spent on task 28 minutes
Notes
not defined yet
Code: 20:35:23 UTC,
sql-postgres,
autosave
Code: 20:35:33 UTC,
sql-postgres,
autosave
Code: 20:36:03 UTC,
sql-postgres,
autosave
Code: 20:36:17 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
row 1: got ('4', '-42'), expected ('2', '-5')
function result: +---+-----+ | 4 | -42 | | 3 | 36 | | 2 | 14 | +---+-----+
function result: +---+-----+ | 4 | -42 | | 3 | 36 | | 2 | 14 | +---+-----+
expand all
User tests
1.
0.044 s
OK
function result: +---+-----+ | 4 | -42 | | 3 | 36 | | 2 | 14 | +---+-----+
function result: +---+-----+ | 4 | -42 | | 3 | 36 | | 2 | 14 | +---+-----+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 20:36:49 UTC,
sql-postgres,
autosave
Code: 20:36:55 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "events.value" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: SELECT event_type, value
^
expand all
User tests
1.
0.060 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "events.value" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: SELECT event_type, value
^
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 20:37:23 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
row 1: got ('3', '20'), expected ('2', '-5')
function result: +---+-----+ | 3 | 20 | | 2 | 2 | | 4 | -42 | | 3 | 16 | | 2 | 7 | | 2 | 5 | +---+-----+
function result: +---+-----+ | 3 | 20 | | 2 | 2 | | 4 | -42 | | 3 | 16 | | 2 | 7 | | 2 | 5 | +---+-----+
expand all
User tests
1.
0.044 s
OK
function result: +---+-----+ | 3 | 20 | | 2 | 2 | | 4 | -42 | | 3 | 16 | | 2 | 7 | | 2 | 5 | +---+-----+
function result: +---+-----+ | 3 | 20 | | 2 | 2 | | 4 | -42 | | 3 | 16 | | 2 | 7 | | 2 | 5 | +---+-----+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 20:37:50 UTC,
sql-postgres,
autosave
Code: 20:37:53 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
row 1: got '4', expected ('2', '-5')
function result: +---+ | 4 | | 3 | | 2 | +---+
function result: +---+ | 4 | | 3 | | 2 | +---+
expand all
User tests
1.
0.040 s
OK
function result: +---+ | 4 | | 3 | | 2 | +---+
function result: +---+ | 4 | | 3 | | 2 | +---+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 20:38:16 UTC,
sql-postgres,
autosave
Code: 20:38:38 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
row 1: got ('4', '2015-05-09 13:19:57'), expected ('2', '-5')
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
expand all
User tests
1.
0.044 s
OK
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 20:39:36 UTC,
sql-postgres,
autosave
Analysis
expand all
Example tests
1.
0.048 s
WRONG ANSWER,
row 1: got ('4', '2015-05-09 13:19:57'), expected ('2', '-5')
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
expand all
User tests
1.
0.044 s
OK
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
syntax error at or near "WHERE"
LINE 6: WHERE time != max(time)
^
expand all
User tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
syntax error at or near "WHERE"
LINE 6: WHERE time != max(time)
^
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
aggregate functions are not allowed in WHERE
LINE 5: WHERE time != max(time)
^
expand all
User tests
1.
0.056 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
aggregate functions are not allowed in WHERE
LINE 5: WHERE time != max(time)
^
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.052 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "events.time" must appear in the GROUP BY clause or be used in an aggregate function
LINE 6: HAVING time != max(time)
^
expand all
User tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "events.time" must appear in the GROUP BY clause or be used in an aggregate function
LINE 6: HAVING time != max(time)
^
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
got 0 rows, expected 2 rows
function result:
function result:
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
row 1: got ('4', '2015-05-09 13:19:57'), expected ('2', '-5')
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
expand all
User tests
1.
0.044 s
OK
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 20:44:34 UTC,
sql-postgres,
autosave
Code: 20:44:44 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
example
example test
example test
✘
WRONG ANSWER
row 1: got ('2', '5', '2015-05-09 12:42:00'), expected ('2', '-5')
row 1: got ('2', '5', '2015-05-09 12:42:00'), expected ('2', '-5')
1.
0.044 s
WRONG ANSWER,
row 1: got ('2', '5', '2015-05-09 12:42:00'), expected ('2', '-5')
function result: +---+-----+---------------------+ | 2 | 5 | 2015-05-09 12:42:00 | | 4 | -42 | 2015-05-09 13:19:57 | | 2 | 2 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 12:54:39 | | 3 | 16 | 2015-05-09 13:19:57 | | 3 | 20 | 2015-05-09 15:01:09 | +---+-----+---------------------+
function result: +---+-----+---------------------+ | 2 | 5 | 2015-05-09 12:42:00 | | 4 | -42 | 2015-05-09 13:19:57 | | 2 | 2 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 12:54:39 | | 3 | 16 | 2015-05-09 13:19:57 | | 3 | 20 | 2015-05-09 15:01:09 | +---+-----+---------------------+
expand all
User tests
1.
0.040 s
OK
function result: +---+-----+---------------------+ | 2 | 5 | 2015-05-09 12:42:00 | | 4 | -42 | 2015-05-09 13:19:57 | | 2 | 2 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 12:54:39 | | 3 | 16 | 2015-05-09 13:19:57 | | 3 | 20 | 2015-05-09 15:01:09 | +---+-----+---------------------+
function result: +---+-----+---------------------+ | 2 | 5 | 2015-05-09 12:42:00 | | 4 | -42 | 2015-05-09 13:19:57 | | 2 | 2 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 12:54:39 | | 3 | 16 | 2015-05-09 13:19:57 | | 3 | 20 | 2015-05-09 15:01:09 | +---+-----+---------------------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 20:44:57 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.048 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
syntax error at or near "BY"
LINE 4: PARTITION BY event_type
^
expand all
User tests
1.
0.048 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
syntax error at or near "BY"
LINE 4: PARTITION BY event_type
^
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 20:45:48 UTC,
sql-postgres,
autosave
Analysis
expand all
Example tests
example
example test
example test
✘
WRONG ANSWER
row 1: got ('2', '5', '2015-05-09 12:42:00', '2015-05-09 14:48:30'), expected ('2', '-5')
row 1: got ('2', '5', '2015-05-09 12:42:00', '2015-05-09 14:48:30'), expected ('2', '-5')
1.
0.060 s
WRONG ANSWER,
row 1: got ('2', '5', '2015-05-09 12:42:00', '2015-05-09 14:48:30'), expected ('2', '-5')
function result: +---+-----+---------------------+---------------------+ | 2 | 5 | 2015-05-09 12:42:00 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 12:54:39 | 2015-05-09 14:48:30 | | 2 | 2 | 2015-05-09 14:48:30 | 2015-05-09 14:48:30 | | 3 | 16 | 2015-05-09 13:19:57 | 2015-05-09 15:01:09 | | 3 | 20 | 2015-05-09 15:01:09 | 2015-05-09 15:01:09 | | 4 | -42 | 2015-05-09 13:19:57 | 2015-05-09 13:19:57 | +---+-----+---------------------+---------------------+
function result: +---+-----+---------------------+---------------------+ | 2 | 5 | 2015-05-09 12:42:00 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 12:54:39 | 2015-05-09 14:48:30 | | 2 | 2 | 2015-05-09 14:48:30 | 2015-05-09 14:48:30 | | 3 | 16 | 2015-05-09 13:19:57 | 2015-05-09 15:01:09 | | 3 | 20 | 2015-05-09 15:01:09 | 2015-05-09 15:01:09 | | 4 | -42 | 2015-05-09 13:19:57 | 2015-05-09 13:19:57 | +---+-----+---------------------+---------------------+
expand all
User tests
1.
0.044 s
OK
function result: +---+-----+---------------------+---------------------+ | 2 | 5 | 2015-05-09 12:42:00 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 12:54:39 | 2015-05-09 14:48:30 | | 2 | 2 | 2015-05-09 14:48:30 | 2015-05-09 14:48:30 | | 3 | 16 | 2015-05-09 13:19:57 | 2015-05-09 15:01:09 | | 3 | 20 | 2015-05-09 15:01:09 | 2015-05-09 15:01:09 | | 4 | -42 | 2015-05-09 13:19:57 | 2015-05-09 13:19:57 | +---+-----+---------------------+---------------------+
function result: +---+-----+---------------------+---------------------+ | 2 | 5 | 2015-05-09 12:42:00 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 12:54:39 | 2015-05-09 14:48:30 | | 2 | 2 | 2015-05-09 14:48:30 | 2015-05-09 14:48:30 | | 3 | 16 | 2015-05-09 13:19:57 | 2015-05-09 15:01:09 | | 3 | 20 | 2015-05-09 15:01:09 | 2015-05-09 15:01:09 | | 4 | -42 | 2015-05-09 13:19:57 | 2015-05-09 13:19:57 | +---+-----+---------------------+---------------------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
syntax error at or near "("
LINE 4: MAX(time) OVER (PARTITION BY event_type)
^
expand all
User tests
1.
0.040 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
syntax error at or near "("
LINE 4: MAX(time) OVER (PARTITION BY event_type)
^
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
example
example test
example test
✘
WRONG ANSWER
row 1: got ('2', '5', '2015-05-09 14:48:30'), expected ('2', '-5')
row 1: got ('2', '5', '2015-05-09 14:48:30'), expected ('2', '-5')
1.
0.060 s
WRONG ANSWER,
row 1: got ('2', '5', '2015-05-09 14:48:30'), expected ('2', '-5')
function result: +---+-----+---------------------+ | 2 | 5 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 14:48:30 | | 2 | 2 | 2015-05-09 14:48:30 | | 3 | 16 | 2015-05-09 15:01:09 | | 3 | 20 | 2015-05-09 15:01:09 | | 4 | -42 | 2015-05-09 13:19:57 | +---+-----+---------------------+
function result: +---+-----+---------------------+ | 2 | 5 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 14:48:30 | | 2 | 2 | 2015-05-09 14:48:30 | | 3 | 16 | 2015-05-09 15:01:09 | | 3 | 20 | 2015-05-09 15:01:09 | | 4 | -42 | 2015-05-09 13:19:57 | +---+-----+---------------------+
expand all
User tests
1.
0.040 s
OK
function result: +---+-----+---------------------+ | 2 | 5 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 14:48:30 | | 2 | 2 | 2015-05-09 14:48:30 | | 3 | 16 | 2015-05-09 15:01:09 | | 3 | 20 | 2015-05-09 15:01:09 | | 4 | -42 | 2015-05-09 13:19:57 | +---+-----+---------------------+
function result: +---+-----+---------------------+ | 2 | 5 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 14:48:30 | | 2 | 2 | 2015-05-09 14:48:30 | | 3 | 16 | 2015-05-09 15:01:09 | | 3 | 20 | 2015-05-09 15:01:09 | | 4 | -42 | 2015-05-09 13:19:57 | +---+-----+---------------------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
syntax error at or near "DISTINCT"
LINE 4: DISTINCT MAX(time) OVER (PARTITION BY event_type)
^
expand all
User tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
syntax error at or near "DISTINCT"
LINE 4: DISTINCT MAX(time) OVER (PARTITION BY event_type)
^
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
example
example test
example test
✘
WRONG ANSWER
row 1: got ('2', '5', '2015-05-09 14:48:30'), expected ('2', '-5')
row 1: got ('2', '5', '2015-05-09 14:48:30'), expected ('2', '-5')
1.
0.044 s
WRONG ANSWER,
row 1: got ('2', '5', '2015-05-09 14:48:30'), expected ('2', '-5')
function result: +---+-----+---------------------+ | 2 | 5 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 14:48:30 | | 2 | 2 | 2015-05-09 14:48:30 | | 3 | 16 | 2015-05-09 15:01:09 | | 3 | 20 | 2015-05-09 15:01:09 | | 4 | -42 | 2015-05-09 13:19:57 | +---+-----+---------------------+
function result: +---+-----+---------------------+ | 2 | 5 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 14:48:30 | | 2 | 2 | 2015-05-09 14:48:30 | | 3 | 16 | 2015-05-09 15:01:09 | | 3 | 20 | 2015-05-09 15:01:09 | | 4 | -42 | 2015-05-09 13:19:57 | +---+-----+---------------------+
expand all
User tests
1.
0.044 s
OK
function result: +---+-----+---------------------+ | 2 | 5 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 14:48:30 | | 2 | 2 | 2015-05-09 14:48:30 | | 3 | 16 | 2015-05-09 15:01:09 | | 3 | 20 | 2015-05-09 15:01:09 | | 4 | -42 | 2015-05-09 13:19:57 | +---+-----+---------------------+
function result: +---+-----+---------------------+ | 2 | 5 | 2015-05-09 14:48:30 | | 2 | 7 | 2015-05-09 14:48:30 | | 2 | 2 | 2015-05-09 14:48:30 | | 3 | 16 | 2015-05-09 15:01:09 | | 3 | 20 | 2015-05-09 15:01:09 | | 4 | -42 | 2015-05-09 13:19:57 | +---+-----+---------------------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "events.value" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3: event_type, value,
^
expand all
User tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "events.value" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3: event_type, value,
^
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.060 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "events.time" must appear in the GROUP BY clause or be used in an aggregate function
LINE 4: MAX(time) OVER (PARTITION BY event_type)
^
expand all
User tests
1.
0.040 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "events.time" must appear in the GROUP BY clause or be used in an aggregate function
LINE 4: MAX(time) OVER (PARTITION BY event_type)
^
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
row 1: got ('2', '2015-05-09 14:48:30'), expected ('2', '-5')
function result: +---+---------------------+ | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 3 | 2015-05-09 15:01:09 | | 3 | 2015-05-09 15:01:09 | | 4 | 2015-05-09 13:19:57 | +---+---------------------+
function result: +---+---------------------+ | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 3 | 2015-05-09 15:01:09 | | 3 | 2015-05-09 15:01:09 | | 4 | 2015-05-09 13:19:57 | +---+---------------------+
expand all
User tests
1.
0.040 s
OK
function result: +---+---------------------+ | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 3 | 2015-05-09 15:01:09 | | 3 | 2015-05-09 15:01:09 | | 4 | 2015-05-09 13:19:57 | +---+---------------------+
function result: +---+---------------------+ | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 3 | 2015-05-09 15:01:09 | | 3 | 2015-05-09 15:01:09 | | 4 | 2015-05-09 13:19:57 | +---+---------------------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
row 1: got ('2', '2015-05-09 14:48:30'), expected ('2', '-5')
function result: +---+---------------------+ | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 3 | 2015-05-09 15:01:09 | | 3 | 2015-05-09 15:01:09 | | 4 | 2015-05-09 13:19:57 | +---+---------------------+
function result: +---+---------------------+ | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 3 | 2015-05-09 15:01:09 | | 3 | 2015-05-09 15:01:09 | | 4 | 2015-05-09 13:19:57 | +---+---------------------+
expand all
User tests
1.
0.044 s
OK
function result: +---+---------------------+ | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 3 | 2015-05-09 15:01:09 | | 3 | 2015-05-09 15:01:09 | | 4 | 2015-05-09 13:19:57 | +---+---------------------+
function result: +---+---------------------+ | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 2 | 2015-05-09 14:48:30 | | 3 | 2015-05-09 15:01:09 | | 3 | 2015-05-09 15:01:09 | | 4 | 2015-05-09 13:19:57 | +---+---------------------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.040 s
WRONG ANSWER,
row 1: got ('4', '2015-05-09 13:19:57'), expected ('2', '-5')
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
expand all
User tests
1.
0.040 s
OK
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
function result: +---+---------------------+ | 4 | 2015-05-09 13:19:57 | | 3 | 2015-05-09 15:01:09 | | 2 | 2015-05-09 14:48:30 | +---+---------------------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
row 1: got ('2', '0:00:00'), expected ('2', '-5')
function result: +---+---------+ | 2 | 0:00:00 | | 3 | 0:00:00 | | 4 | 0:00:00 | +---+---------+
function result: +---+---------+ | 2 | 0:00:00 | | 3 | 0:00:00 | | 4 | 0:00:00 | +---+---------+
expand all
User tests
1.
0.044 s
OK
function result: +---+---------+ | 2 | 0:00:00 | | 3 | 0:00:00 | | 4 | 0:00:00 | +---+---------+
function result: +---+---------+ | 2 | 0:00:00 | | 3 | 0:00:00 | | 4 | 0:00:00 | +---+---------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 5: ORDER BY time DESC
^
expand all
User tests
1.
0.040 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 5: ORDER BY time DESC
^
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 20:54:27 UTC,
sql-postgres,
autosave
Analysis
expand all
Example tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
syntax error at or near "OVER"
LINE 3: OVER (
^
expand all
User tests
1.
0.040 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
syntax error at or near "OVER"
LINE 3: OVER (
^
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
row 1: got ('2', 'NULL'), expected ('2', '-5')
function result: +---+------+ | 2 | NULL | | 2 | 7 | | 2 | 7 | | 3 | NULL | | 3 | 16 | | 4 | NULL | +---+------+
function result: +---+------+ | 2 | NULL | | 2 | 7 | | 2 | 7 | | 3 | NULL | | 3 | 16 | | 4 | NULL | +---+------+
expand all
User tests
1.
0.048 s
OK
function result: +---+------+ | 2 | NULL | | 2 | 7 | | 2 | 7 | | 3 | NULL | | 3 | 16 | | 4 | NULL | +---+------+
function result: +---+------+ | 2 | NULL | | 2 | 7 | | 2 | 7 | | 3 | NULL | | 3 | 16 | | 4 | NULL | +---+------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
row 1: got ('2', 'NULL'), expected ('2', '-5')
function result: +---+------+ | 2 | NULL | | 2 | 9 | | 2 | 9 | | 3 | NULL | | 3 | 36 | | 4 | NULL | +---+------+
function result: +---+------+ | 2 | NULL | | 2 | 9 | | 2 | 9 | | 3 | NULL | | 3 | 36 | | 4 | NULL | +---+------+
expand all
User tests
1.
0.044 s
OK
function result: +---+------+ | 2 | NULL | | 2 | 9 | | 2 | 9 | | 3 | NULL | | 3 | 36 | | 4 | NULL | +---+------+
function result: +---+------+ | 2 | NULL | | 2 | 9 | | 2 | 9 | | 3 | NULL | | 3 | 36 | | 4 | NULL | +---+------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
row 1: got ('2', 'NULL'), expected ('2', '-5')
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
expand all
User tests
1.
0.060 s
OK
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 20:58:00 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
got 0 rows, expected 2 rows
function result:
function result:
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 20:58:25 UTC,
sql-postgres,
autosave
Code: 20:58:28 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "tmp" does not exist
LINE 8: WHERE tmp != NULL
^
expand all
User tests
1.
0.040 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "tmp" does not exist
LINE 8: WHERE tmp != NULL
^
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 20:58:40 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.040 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "tmp" does not exist
LINE 8: WHERE tmp IS NOT NULL
^
expand all
User tests
1.
0.040 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "tmp" does not exist
LINE 8: WHERE tmp IS NOT NULL
^
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 20:59:00 UTC,
sql-postgres,
autosave
Code: 20:59:04 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
row 1: got ('2', 'NULL'), expected ('2', '-5')
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
expand all
User tests
1.
0.044 s
OK
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 20:59:30 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "tmp" does not exist
LINE 8: WHERE tmp IS NOT NULL
^
expand all
User tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "tmp" does not exist
LINE 8: WHERE tmp IS NOT NULL
^
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 20:59:44 UTC,
sql-postgres,
autosave
Code: 20:59:55 UTC,
sql-postgres,
autosave
Code: 21:00:01 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
row 1: got ('2', 'NULL'), expected ('2', '-5')
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
expand all
User tests
1.
0.044 s
OK
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 21:00:25 UTC,
sql-postgres,
autosave
Code: 21:00:31 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.044 s
WRONG ANSWER,
row 1: got ('2', 'NULL'), expected ('2', '-5')
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
expand all
User tests
1.
0.044 s
OK
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
function result: +---+------+ | 2 | NULL | | 2 | -5 | | 2 | -5 | | 3 | NULL | | 3 | 4 | | 4 | NULL | +---+------+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 21:00:57 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "dif" does not exist
LINE 8: WHERE dif IS NOT NULL
^
expand all
User tests
1.
0.044 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column "dif" does not exist
LINE 8: WHERE dif IS NOT NULL
^
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 21:01:40 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.052 s
WRONG ANSWER,
row 2: got ('2', '-5'), expected ('3', '4')
function result: +---+----+ | 2 | -5 | | 2 | -5 | | 3 | 4 | +---+----+
function result: +---+----+ | 2 | -5 | | 2 | -5 | | 3 | 4 | +---+----+
expand all
User tests
1.
0.052 s
OK
function result: +---+----+ | 2 | -5 | | 2 | -5 | | 3 | 4 | +---+----+
function result: +---+----+ | 2 | -5 | | 2 | -5 | | 3 | 4 | +---+----+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 21:01:52 UTC,
sql-postgres,
autosave
Code: 21:01:54 UTC,
sql-postgres,
verify,
result: Failed
Analysis
expand all
Example tests
1.
0.060 s
WRONG ANSWER,
row 1: got ('3', '4'), expected ('2', '-5')
function result: +---+----+ | 3 | 4 | | 2 | -5 | +---+----+
function result: +---+----+ | 3 | 4 | | 2 | -5 | +---+----+
expand all
User tests
1.
0.060 s
OK
function result: +---+----+ | 3 | 4 | | 2 | -5 | +---+----+
function result: +---+----+ | 3 | 4 | | 2 | -5 | +---+----+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 21:02:28 UTC,
sql-postgres,
autosave
Code: 21:02:37 UTC,
sql-postgres,
verify,
result: Passed
-- Implement your solution here
WITH t1 AS
(SELECT event_type, nth_value(value, 1) OVER (
PARTITION BY event_type ORDER BY time DESC
) - nth_value(value, 2) OVER (
PARTITION BY event_type ORDER BY time DESC
) AS dif
FROM events)
SELECT DISTINCT * FROM t1 WHERE dif IS NOT NULL ORDER BY event_type
Analysis
expand all
Example tests
1.
0.044 s
OK
function result: +---+----+ | 2 | -5 | | 3 | 4 | +---+----+
function result: +---+----+ | 2 | -5 | | 3 | 4 | +---+----+
expand all
User tests
1.
0.044 s
OK
function result: +---+----+ | 2 | -5 | | 3 | 4 | +---+----+
function result: +---+----+ | 2 | -5 | | 3 | 4 | +---+----+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 21:02:53 UTC,
sql-postgres,
verify,
result: Passed
-- Implement your solution here
WITH t1 AS
(SELECT event_type, nth_value(value, 1) OVER (
PARTITION BY event_type ORDER BY time DESC
) - nth_value(value, 2) OVER (
PARTITION BY event_type ORDER BY time DESC
) AS dif
FROM events)
SELECT DISTINCT * FROM t1 WHERE dif IS NOT NULL ORDER BY event_type
Analysis
expand all
Example tests
1.
0.044 s
OK
function result: +---+----+ | 2 | -5 | | 3 | 4 | +---+----+
function result: +---+----+ | 2 | -5 | | 3 | 4 | +---+----+
expand all
User tests
1.
0.044 s
OK
function result: +---+----+ | 2 | -5 | | 3 | 4 | +---+----+
function result: +---+----+ | 2 | -5 | | 3 | 4 | +---+----+
User test case 1:
insert into events values(2, 5, '2015-05-09 12:42:00'); insert into events values(4, -42, '2015-05-09 13:19:57'); insert into events values(2, 2, '2015-05-09 14:48:30'); insert into events values(2, 7, '2015-05-09 12:54:39'); insert into events values(3, 16, '2015-05-09 13:19:57'); insert into events values(3, 20, '2015-05-09 15:01:09');
Code: 21:03:04 UTC,
sql-postgres,
final,
score:
100
-- Implement your solution here
WITH t1 AS
(SELECT event_type, nth_value(value, 1) OVER (
PARTITION BY event_type ORDER BY time DESC
) - nth_value(value, 2) OVER (
PARTITION BY event_type ORDER BY time DESC
) AS dif
FROM events)
SELECT DISTINCT * FROM t1 WHERE dif IS NOT NULL ORDER BY event_typeAnalysis summary
The solution obtained perfect score.
Analysis
expand all
Example tests
1.
0.048 s
OK
function result: +---+----+ | 2 | -5 | | 3 | 4 | +---+----+
function result: +---+----+ | 2 | -5 | | 3 | 4 | +---+----+
expand all
Correctness tests
1.
0.044 s
OK
1.
0.044 s
OK
2.
0.044 s
OK
1.
0.044 s
OK
2.
0.044 s
OK
1.
0.044 s
OK
1.
0.044 s
OK
1.
0.044 s
OK
2.
0.044 s
OK
1.
0.044 s
OK
1.
0.044 s
OK
1.
0.044 s
OK
1.
0.044 s
OK
1.
0.044 s
OK