The Dev Log › Tips & Cheat Codes

SQL Cheat Codes: Queries Every Developer Should Know

By Jezer Niel Blanca, Full Stack Developer · August 9, 2026 · 4 min read

Window functions, CTEs, upserts, and the other SQL patterns that save me from writing loops in application code.

ORMs are wonderful, but knowing raw SQL well makes you a better developer, even if you mostly write Eloquent. When I understand the query underneath, I can spot slow pages faster and replace entire loops in application code with a single statement. These are the patterns I use most. Examples work in MySQL 8+ and PostgreSQL unless noted.

Filter Groups with HAVING

WHERE filters rows before grouping. HAVING filters after:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY customer_id
HAVING COUNT(*) >= 5;

Readable Queries with CTEs

Common table expressions let you name intermediate results so complex queries read top to bottom:

WITH monthly AS (
    SELECT customer_id, SUM(total) AS spent
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY customer_id
)
SELECT c.name, m.spent
FROM monthly m
JOIN customers c ON c.id = m.customer_id
ORDER BY m.spent DESC
LIMIT 10;

In MySQL, write the interval as INTERVAL 30 DAY.

Window Functions Are a Superpower

Window functions compute across related rows without collapsing them. Rank products within each category:

SELECT
    category_id,
    name,
    price,
    RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS price_rank
FROM products;

Running totals are just as easy:

SELECT
    created_at::date AS day,
    SUM(total) AS daily_total,
    SUM(SUM(total)) OVER (ORDER BY created_at::date) AS running_total
FROM orders
GROUP BY created_at::date;

In MySQL, use DATE(created_at) instead of the ::date cast.

Latest Row Per Group

One of the most common questions in real apps: "show each user's most recent order."

SELECT *
FROM (
    SELECT
        o.*,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders o
) ranked
WHERE rn = 1;

If you're doing this in a loop in PHP or JavaScript, a window function can probably replace it.

Conditional Aggregation

Pivot statuses into columns with CASE inside an aggregate:

SELECT
    DATE(created_at) AS day,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,
    SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded
FROM orders
GROUP BY DATE(created_at);

One query gives you a whole dashboard row per day.

Find Rows with No Match

Customers who have never ordered:

SELECT c.*
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

NOT EXISTS handles NULL values more predictably than NOT IN.

Upserts

Insert or update in one statement. PostgreSQL:

INSERT INTO prices (sku, amount)
VALUES ('A-100', 1200)
ON CONFLICT (sku) DO UPDATE SET amount = EXCLUDED.amount;

MySQL:

INSERT INTO prices (sku, amount)
VALUES ('A-100', 1200)
ON DUPLICATE KEY UPDATE amount = VALUES(amount);

Newer MySQL versions prefer a row alias (AS new ... amount = new.amount), but VALUES() still works.

Find Duplicates

Before adding a unique index, find what would violate it:

SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Ask the Database How It Plans to Run a Query

When a query is slow, don't guess:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;

Look for full table scans on large tables. Often the fix is a composite index that matches your filter and sort:

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);

Habits That Keep SQL Safe

Always use parameter binding; never concatenate user input into SQL.
Wrap multi-step writes in a transaction.
Run destructive UPDATE or DELETE statements as a SELECT first.
Add indexes based on real query plans, not hunches.

Wrapping Up

You don't need to become a database administrator. Knowing these patterns is enough to write faster features and debug slow pages with confidence.

Have a slow app or a reporting feature you've been putting off? Let's talk. I enjoy making data fast and useful.

Tags: SQL, MySQL, PostgreSQL, Database