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
Task timeline
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
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', '-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 | +---+-----+
Code: 20:36:49 UTC,
sql-postgres,
autosave
Code: 20:36:55 UTC,
sql-postgres,
verify,
result: Failed
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 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
^
Code: 20:37:23 UTC,
sql-postgres,
verify,
result: Failed
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 ('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 | +---+-----+
Code: 20:37:50 UTC,
sql-postgres,
autosave
Code: 20:37:53 UTC,
sql-postgres,
verify,
result: Failed
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', 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 | +---+
Code: 20:38:16 UTC,
sql-postgres,
autosave
Code: 20:38:38 UTC,
sql-postgres,
verify,
result: Failed
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 | +---+---------------------+
Code: 20:39:36 UTC,
sql-postgres,
autosave
Code: 20:39:56 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
WITH t1 as (
SELECT event_type, max(time)
FROM events
GROUP BY event_type
)
SELECT * from t1
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.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 | +---+---------------------+
Code: 20:40:55 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
WITH t1 as (
SELECT event_type, max(time)
FROM events
GROUP BY event_type
WHERE time != max(time)
)
SELECT * from t1
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)
^
Code: 20:41:10 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
WITH t1 as (
SELECT event_type, max(time)
FROM events
WHERE time != max(time)
GROUP BY event_type
)
SELECT * from t1
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)
^
Code: 20:41:36 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
WITH t1 as (
SELECT event_type, max(time)
FROM events
GROUP BY event_type
HAVING time != max(time)
)
SELECT * from t1
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)
^
Code: 20:42:00 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
WITH t1 as (
SELECT event_type, max(time)
FROM events
GROUP BY event_type, events.time
HAVING events.time != max(time)
)
SELECT * from t1
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:
Code: 20:43:27 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
WITH max1 as (
SELECT event_type, max(time)
FROM events
GROUP BY event_type
)
SELECT * from max1
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 | +---+---------------------+
Code: 20:44:34 UTC,
sql-postgres,
autosave
Code: 20:44:44 UTC,
sql-postgres,
verify,
result: Failed
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 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 | +---+-----+---------------------+
Code: 20:44:57 UTC,
sql-postgres,
verify,
result: Failed
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.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
^
Code: 20:45:48 UTC,
sql-postgres,
autosave
Code: 20:46:37 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
SELECT
*,
MAX(time) OVER (PARTITION BY event_type)
FROM events
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 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 | +---+-----+---------------------+---------------------+
Code: 20:46:49 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
SELECT
event_type, value
MAX(time) OVER (PARTITION BY event_type)
FROM events
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)
^
Code: 20:47:09 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
SELECT
event_type, value,
MAX(time) OVER (PARTITION BY event_type)
FROM events
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 | +---+-----+---------------------+
Code: 20:48:11 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
SELECT
event_type, value,
DISTINCT MAX(time) OVER (PARTITION BY event_type)
FROM events
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)
^
Code: 20:48:23 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
SELECT
event_type, value,
MAX(time) OVER (PARTITION BY event_type)
FROM events
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 | +---+-----+---------------------+
Code: 20:48:57 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
SELECT
event_type, value,
MAX(time) OVER (PARTITION BY event_type)
FROM events
GROUP 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
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,
^
Code: 20:49:13 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
SELECT
event_type,
MAX(time) OVER (PARTITION BY event_type)
FROM events
GROUP 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.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)
^
Code: 20:49:30 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
SELECT
event_type,
MAX(time) OVER (PARTITION BY event_type)
FROM events
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 | +---+---------------------+
Code: 20:51:32 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
SELECT
event_type,
MAX(time) OVER (PARTITION BY event_type)
FROM events
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 | +---+---------------------+
Code: 20:51:56 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
SELECT DISTINCT
event_type,
MAX(time) OVER (PARTITION BY event_type)
FROM events
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 | +---+---------------------+
Code: 20:52:07 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
SELECT DISTINCT
event_type,
MAX(time) OVER (PARTITION BY event_type) - MAX(time) OVER (PARTITION BY event_type)
FROM events
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 | +---+---------+
Code: 20:54:13 UTC,
sql-postgres,
verify,
result: Failed
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
^
Code: 20:54:27 UTC,
sql-postgres,
autosave
Code: 20:55:14 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
SELECT *
OVER (
PARTITION BY event_type ORDER BY time DESC
)
FROM events
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 "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 (
^
Code: 20:55:53 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
SELECT event_type, nth_value(value, 2) OVER (
PARTITION BY event_type ORDER BY time DESC
)
FROM events
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 | +---+------+
Code: 20:57:01 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
SELECT event_type, nth_value(value, 2) OVER (
PARTITION BY event_type ORDER BY time DESC
) + nth_value(value, 1) OVER (
PARTITION BY event_type ORDER BY time DESC
)
FROM events
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 | +---+------+
Code: 20:57:34 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
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
)
FROM events
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 | +---+------+
Code: 20:58:00 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
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
)
FROM events
WHERE 2 != 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,
got 0 rows, expected 2 rows
function result:
function result:
Code: 20:58:25 UTC,
sql-postgres,
autosave
Code: 20:58:28 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
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 tmp
FROM events
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');
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
^
Code: 20:58:40 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
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 tmp
FROM events
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');
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
^
Code: 20:59:00 UTC,
sql-postgres,
autosave
Code: 20:59:04 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
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
)
FROM events
WHERE 2 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');
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 | +---+------+
Code: 20:59:30 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
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 tmp
FROM events
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');
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
^
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
-- Implement your solution here
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
)
FROM events
WHERE 2 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');
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 | +---+------+
Code: 21:00:25 UTC,
sql-postgres,
autosave
Code: 21:00:31 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
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
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.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 | +---+------+
Code: 21:00:57 UTC,
sql-postgres,
verify,
result: Failed
-- Implement your solution here
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
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');
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
^
Code: 21:01:40 UTC,
sql-postgres,
verify,
result: Failed
-- 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 * FROM t1 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');
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 | +---+----+
Code: 21:01:52 UTC,
sql-postgres,
autosave
Code: 21:01:54 UTC,
sql-postgres,
verify,
result: Failed
-- 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
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
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 | +---+----+
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
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
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 | +---+----+
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
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
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 | +---+----+
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