Why does my query return errors on Codility's test editor?

Question

My solution to Codility / Exercises 6 SQL / SqlEventsDelta in SQLite works in local DB Browser but not in online Codility test editor. How can I solve this? I want to use my own SQLite code:

WITH cte1 AS
(
    SELECT *, CASE WHEN e2.event_type = e2.prev THEN 0
                 WHEN e2.event_type = e2.next THEN 0
                 ELSE 1 END AS grp
    FROM (SELECT *, LAG(e1.event_type) OVER(ORDER BY (SELECT 1)) AS prev , LEAD(e1.event_type) OVER(ORDER BY (SELECT 1)) AS next FROM events e1) e2
)
,cte2 AS
(
    SELECT cte1.event_type, cte1.time, cte1.grp, cte1.value - LAG(cte1.value) OVER(ORDER BY cte1.event_type, cte1.time) AS value
    FROM cte1
    WHERE cte1.grp = 0
    ORDER BY cte1.event_type, cte1.time
)

SELECT c2.event_type, c2.value
FROM cte2 c2
WHERE (c2.event_type, c2.time) IN (
    SELECT c2.event_type, MAX(c2.time) AS time
    FROM cte2 c2
    GROUP BY c2.event_type)
GROUP BY c2.event_type
ORDER BY c2.event_type, c2.time

It ran on DB Browser for SQLite Version 3.12.2 without error:

event_type | value
-----------+-----------
2          | -5
3          | 4

Execution finished without errors.
Result: 2 rows returned in 7ms

But on the Codility test editor (SQLite Version 3.11.0) I am getting errors:

| Compilation successful.

| Example test:   (example test)
| Output (stderr):
| error on query: ...
| ...
| ...,
| details: near "(": syntax error
| RUNTIME ERROR (tested program terminated with exit code 1)

Detected some errors.

Answer 1

I tried to use a somehow naive approach. I'm aware that it is very bad for performance due to many subqueries but the catch here is the "DISTINCT ON" of PostgreSQL, however I got 100%

Hope you like it!

select distinct on (event_type) event_type, result * -1
from (select event_type, value, lead(value) over (order by event_type) - value result
      from (select *
            from events
            where event_type in (select event_type
                                 from events
                                 group by event_type
                                 having count(event_type) >= 2)
            order by event_type, time desc) a) b

Answer 2

Adding another answer involving self joins -

PostgreSQL

-- write your code in PostgreSQL 9.4

WITH TotalRowCount AS (
    SELECT
        event_type,
        COUNT(*) as row_count
    FROM events
    GROUP BY 1
),

RankedEventType AS (
    SELECT
        event_type,
        value,
        ROW_NUMBER() OVER(PARTITION BY event_type ORDER BY time) as row_num
    FROM events
)


SELECT
    a.event_type,
    a.value - b.value as value
FROM RankedEventType a
INNER JOIN TotalRowCount c
    ON a.event_type = c.event_type
INNER JOIN RankedEventType b
    ON a.event_type = b.event_type
WHERE 1 = 1
AND a.row_num = c.row_count
AND b.row_num = c.row_count - 1
ORDER BY 1

Answer 3

without nested queries, got 100%

with data as (
with count as (select event_type
                                 from events
                                 group by event_type
                                 having count(event_type) >= 2)
select e.event_type , e.value, e.time from events as e inner join count as r on e.event_type=r.event_type  order by e.event_type, e.time desc                               
)
select distinct on (event_type) event_type,
           value - (LEAD(value) over (order by event_type))  result from data

Answer 4

with data as (SELECT a.event_type, a.value, a.time,
 --Produce a virtual table that stores the next and previous values for each event_type.
LEAD(a.value,1) over (PARTITION by a.event_type ORDER by 'event_type', 'time' DESC) as recent_val,
LAG(a.value,1) over (PARTITION by a.event_type ORDER by 'event_type', 'time' DESC) as penult_val
   
    from events a
    
    JOIN (SELECT event_type 
            from events --Filter the initial dataset for duplicates. Store in correct order
                group by event_type HAVING COUNT(*) > 1 
                    ORDER by event_type) b
        
        on a.event_type = b.event_type) --Compare the virtual table to the filtered dataset

SELECT event_type, ("value"-"penult_val") as diff --Perform the desired arithematic
    from data 
    where recent_val is NULL --Filter for the most recent value

Hi team! This one's my answer. It's largely a goopy conglomerate of the answers above, but it reads more simply and it's commented for context. Being a newbie, I hope it helps other newbies.

Answer 5

My solution:

Get table with rank 1, 2 group by event_type:

with t2 as(
select event_type, value, rank from (
    select event_type, value,
        rank() over(
        partition by event_type
        order by time desc) as rank,
        count(*) over (partition by event_type) as count
    from events) as t
where t.rank <= 2 and t.count > 1
)

Calculate diff using Lead() and filter out null diff with max:

select t3.event_type, max(t3.diff) from (
    select event_type, 
    value - lead(value, 1) over (
        partition by event_type
        order by rank) as diff
    from t2) as t3
group by t3.event_type

Answer 6

I was able to solve it in this way:

select
    event_type
    ,delta
from ( 
    select
        *
        ,rn_calc - lead(value) over(partition by event_type order by rn asc)    delta
    from (
        select 
            event_type
            ,max(value) over(partition by event_type order by time desc)            rn_calc
            ,row_number() over(partition by event_type order by time desc)          rn
            ,value
            --,time
        from events
    ) t
    where rn in (1,2)
) t
where rn = 1 and delta is not null

Answer 7

I do have the same problem when using the sqlite. Try using below code with PostgreSQL

with data as (select 
e.event_type,
e.value,
e.time,
lead(e.value,1) over (PARTITION by e.event_type order by e.event_type,e.time asc) as next_val,
lag (e.value,1) over (PARTITION by e.event_type order by e.event_type,e.time asc) as prev_val
from events e)
select distinct d.event_type, (d.value-d.prev_val) as diff
from 
events e,data d
where e.event_type = d.event_type
and d.next_val is null
and e.event_type in ( SELECT event_type
                        from data 
                        group by 
                        event_type
                        having count(1) > 1)
order by 1;

Answer 8

Solution with one subquery

WITH diff AS
  (SELECT event_type,
          value,
          LEAD(value) OVER (PARTITION BY event_type
                            ORDER BY TIME DESC) AS prev
   FROM EVENTS
   GROUP BY event_type,
            value,
            time
)

SELECT DISTINCT ON (event_type) event_type,
                   value - prev
FROM diff
WHERE prev IS NOT NULL;

Answer 9

I try this on MSSQL and it works as well

 SELECT event_type, 
        MAX(value) - MAX(prev_value) AS value_difference
 FROM (
        SELECT event_type, 
               value, 
               L.AG(value) OVER (PARTITION BY event_type ORDER BY time) AS prev_value
        FROM events) AS subquery
 GROUP BY event_type
 HAVING COUNT(*) > 1
 ORDER BY event_type;

Answer 10

--in PostgreSQL 9.4

with ct1 as (SELECT 
    event_type,
    value,
    time,
    rank() over (partition by event_type order by time desc) as rank
from events),
ct2 as (
select event_type, value, rank, lag (value,1) over (order by event_type) as previous_value
from ct1
order by event_type)
select event_type, previous_value - value from ct2
where rank = 2
order by event_type

Answer 11

with deltas as (
  select distinct event_type, 
     first_value(value) over (PARTITION by event_type ORDER by time DESC) - 
     nth_value(value, 2) over (PARTITION by event_type ORDER by time DESC) as delta
    from events
)
select * from deltas where delta is not null order by 1;

Answer 12

--1) "Minimalist"old style solution (no use of window functions) WITH maxevents AS (Select event_type as Et1, max(time) as maxtime from events group by event_type having count(1)>1), prevevents AS (Select event_type as Et2, max(time) as nextime from events JOIN maxevents ON event_type=Et1 AND time<maxtime group by event_type), maxeventvalues AS (Select event_type, value as v1 from events JOIN maxevents ON event_type=et1 AND time=maxtime), preveventvalues AS (Select event_type as et, value as v2 from events JOIN prevevents ON event_type=et2 AND time=nextime)

SELECT event_type, v1-v2 as value FROM maxeventvalues JOIN preveventvalues ON event_type=et Order by event_type asc ; --2) modern solution using window functions WITH maxevents AS (Select event_type as Et1, max(time) as maxtime from events group by event_type having count(1)>1) ,AllValDifs as (SELECT event_type, value- LAG(value,1,0) OVER (PARTITION BY event_type ORDER BY time ASC) as ValDif , time FROM events JOIN maxevents ON event_type=et1) SELECT DISTINCT event_type,FIRST_VALUE ( ValDif )
OVER (PARTITION BY event_type ORDER BY time DESC) from AllValDifs ORDER BY event_type ASC;