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

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

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.

Solution

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