Stunning SQL Queries: From Tetris Game to Real‑Time Funnels

SQL can be more than just data retrieval; it can solve complex problems with elegance and performance. The article gathers several standout examples that illustrate this power.

Tetris in PostgreSQL

A full‑featured Tetris game is written entirely as a recursive Common Table Expression (CTE) in PostgreSQL. The query defines board dimensions, piece shapes, movement logic, collision detection, line clearing, scoring, and rendering, all within a single SQL statement. The code demonstrates how recursive CTEs can model iterative processes and how dblink can be used to read user input within the same query.

-- Tetris game query (excerpt)
WITH RECURSIVE main AS (
    -- constant parameters
    WITH const AS (
        SELECT 10 AS width, 20 AS height, 60 AS fps,
               48/60.0 AS init_drop_delta,
               6/60.0 AS min_drop_delta,
               2/60.0 AS drop_delta_decrease,
               10 AS lines_per_level,
               1 AS level_score_multiplier
    ),
    -- piece definitions
    tetromino(id, rotation, piece) AS (
        SELECT * FROM const c(w), LATERAL (
            VALUES
                (0,0,ARRAY[4,5,(c.w+1)+4,(c.w+1)+5]),
                (1,0,ARRAY[3,4,5,6]),
                (2,0,ARRAY[3,4,5,(c.w+1)+4]),
                (3,0,ARRAY[3,4,5,(c.w+1)+3]),
                (4,0,ARRAY[3,4,5,(c.w+1)+5]),
                (5,0,ARRAY[4,5,(c.w+1)+3,(c.w+1)+4]),
                (6,0,ARRAY[3,4,(c.w+1)+4,(c.w+1)+5])
        ) _(id, rotation, piece)
    ),
    -- initial board state
    SELECT 0 AS frame,
           string_to_array(repeat(repeat('f', const.width)||'t', const.height), NULL)::bool[] AS board,
           0 AS score,
           0 AS lines,
           const.init_drop_delta AS drop_delta,
           (SELECT ARRAY[id,0,0,0] FROM tetromino ORDER BY random() LIMIT 1) AS pos,
           (SELECT id FROM tetromino ORDER BY random() LIMIT 1) AS next_piece,
           clock_timestamp() AS last_drop_time,
           clock_timestamp() AS last_input_time,
           notify('start'),
           pg_sleep(0),
           clock_timestamp() AS last_frame_time
    FROM const
    UNION ALL
    -- recursive step (movement, rendering, sleep, etc.)
    SELECT ...
) SELECT 'score: ' || max(score) AS game_over FROM main;

Session "Reverse‑Three‑Step" with Window Functions

For an e‑commerce activity‑monitoring task, a concise query uses ROW_NUMBER() to order events per user and then joins the latest purchase with the three preceding views. The window function replaces multiple joins and intermediate tables, reducing execution time on Hive to about 20 minutes.

WITH user_events AS (
  SELECT user_id, event_time, event_type, product_id,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) AS rn
  FROM app_events
  WHERE event_time >= date_sub(current_date, 30)
)
SELECT a.user_id,
       a.event_time AS buy_time,
       a.product_id AS buy_product,
       b.product_id AS view_product
FROM user_events a
JOIN user_events b
  ON a.user_id = b.user_id
 AND b.event_time < a.event_time
WHERE a.event_type = 'buy'
  AND b.rn BETWEEN a.rn + 1 AND a.rn + 3;

Real‑Time Funnel in ClickHouse

A single ClickHouse query computes a three‑step funnel (signup → click → pay) in seconds, avoiding batch ETL pipelines. It uses maxIf and countIf to derive the highest step per user and aggregates the counts.

SELECT countIf(step = 1) AS signup,
       countIf(step >= 2) AS click,
       countIf(step >= 3) AS pay
FROM (
  SELECT user_id,
         maxIf(1, action = 'signup') AS step1,
         maxIf(2, action = 'click')  AS step2,
         maxIf(3, action = 'pay')   AS step3,
         greatest(step1, step2, step3) AS step
  FROM events
  WHERE event_time > now() - INTERVAL 1 HOUR
  GROUP BY user_id
);

Dynamic WHERE Clause Generation

When many optional filter columns exist, building the WHERE clause dynamically inside a stored procedure can dramatically improve readability and performance compared to concatenating strings manually.

DECLARE @WHERE VARCHAR(MAX) = '1=1';
IF @TITLE <> ''
  SET @WHERE += ' AND Title = @TITLE';
IF @AGE <> ''
  SET @WHERE += ' AND Age = @Age';
EXEC('SELECT * FROM USER WHERE ' + @WHERE);

-- Improved version using conditional predicates
SELECT *
FROM USER
WHERE ((@TITLE <> '' AND Title = @TITLE) OR @TITLE = '')
  AND ((@Age   <> '' AND Age   = @Age)   OR @Age   = '');

Recursive Employee Hierarchy

A classic recursive CTE lists all subordinates of a manager, regardless of depth, illustrating how hierarchical data can be traversed with a single query.

WITH RECURSIVE Subordinates AS (
  SELECT employee_id, manager_id, employee_name
  FROM Employees
  WHERE manager_id IS NULL   -- CEO
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.employee_name
  FROM Employees e
  INNER JOIN Subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM Subordinates;

Key Takeaways

Window functions are essential for OLAP workloads; they can replace many intermediate tables and stored procedures.

The choice of database engine (Hive, ClickHouse, PostgreSQL, MySQL) dramatically affects execution plans and performance for the same SQL.

Keeping queries concise—removing unnecessary joins, aggregations, or subqueries—often yields faster execution.

Recursive CTEs enable modeling of iterative or hierarchical processes directly in SQL.