Can SQL Run Tetris? Exploring a Recursive CTE Tetris Implementation

The post shares one of the most eye‑catching SQL queries ever seen: a complete implementation of the classic Tetris game written entirely in PostgreSQL using recursive Common Table Expressions (CTEs). The author notes that the code originates from a GitHub project (https://github.com/nuno-faria/tetris-sql) and includes a brief description of the game mechanics.

Key Parts of the Tetris Query

The script creates an unlogged table Input to store user commands, defines a notify function that uses RAISE NOTICE to output text, and loads the dblink extension for intra‑query communication. The main game loop is a recursive CTE named main that iterates frame‑by‑frame, handling input, piece movement, collision detection, line clearing, scoring, level progression, and rendering.

-- Copyright (c) 2024 nuno-faria
-- This software is released under the MIT License.
-- https://opensource.org/licenses/MIT

--- Prerequisites ---
-- Table to store the inputs. cmd stores the key; ts stores the timestamp when the key was pressed.
CREATE UNLOGGED TABLE IF NOT EXISTS Input (cmd char, ts timestamp);
TRUNCATE Input;
INSERT INTO Input VALUES ('', now());

CREATE OR REPLACE FUNCTION notify(str varchar) RETURNS void AS $$
BEGIN
    RAISE NOTICE '%', str;
END
$$ LANGUAGE PLPGSQL;

CREATE EXTENSION IF NOT EXISTS dblink;

--- Tetris game query ---
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
    ),
    -- ... (omitted for brevity) ...
    SELECT 'score: ' || max(score) AS game_over FROM main
);

The author mentions a known issue: when the query runs for an extended period, disk usage continuously grows and eventually causes the process to crash.

Other Impressive SQL Snippets

Beyond the Tetris demo, the article presents several useful examples:

Recursive query to list all subordinates of an employee regardless of hierarchy depth:

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

CASE expression for age grouping:

SELECT name, age,
       CASE WHEN age < 18 THEN '未成年'
            WHEN age >= 18 AND age < 65 THEN '成年'
            ELSE '老年' END AS age_group
FROM customers;

Window function for cumulative sum:

SELECT date, revenue,
       SUM(revenue) OVER (ORDER BY date) AS cumulative_sum
FROM sales;

CROSS JOIN to generate all combinations:

SELECT A.column_name, B.column_name
FROM table_A A CROSS JOIN table_B B;

Recursive query to build a tree path:

WITH RECURSIVE tree_path AS (
    SELECT id, name, CAST(name AS VARCHAR(255)) AS path FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, CONCAT(tp.path, ' > ', c.name)
    FROM categories c JOIN tree_path tp ON c.parent_id = tp.id
)
SELECT id, name, path FROM tree_path;

SQL Best‑Practice Checklist

The article concludes with a concise checklist for writing high‑quality SQL statements:

Keep statements concise and readable.

Avoid overly complex logic and deep nesting; prefer JOINs or window functions.

Choose appropriate data types that match the data.

Maintain consistent formatting (indentation, spacing, line breaks).

Use meaningful table and column names following team conventions.

Apply short, descriptive aliases where helpful.

Write clear comments that explain purpose and functionality; keep them up‑to‑date.

Avoid SELECT * to limit data transfer.

Watch for performance pitfalls such as full table scans; use indexes wisely.

Wrap related operations in transactions and consider isolation levels.

Develop test cases for queries, verify correctness and performance, and conduct peer reviews.