SQL с нуля — шпаргалка для аналитика

Зачем это знать

Начинаете SQL? Эта шпаргалка — от нуля до middle за одну страницу. Reference during learning.

Basic syntax

SELECT

SELECT column FROM TABLE;
SELECT * FROM TABLE;
SELECT col1, col2 FROM TABLE;
SELECT DISTINCT col FROM TABLE;

WHERE

SELECT * FROM users WHERE age > 30;
SELECT * FROM users WHERE country IN ('RU', 'US');
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE created_at BETWEEN '2026-01-01' AND '2026-04-01';
SELECT * FROM users WHERE deleted_at IS NULL;

Logical:

WHERE age > 30 AND country = 'RU'
WHERE age > 30 OR age < 10
NOT (...)

ORDER BY

SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age DESC;
SELECT * FROM users ORDER BY country, age DESC;

LIMIT

SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;

Aggregation

Functions

COUNT(*) - count ROWS
COUNT(col) - count non-NULL
COUNT(DISTINCT col) - unique VALUES
SUM(col)
AVG(col)
MIN(col) / MAX(col)

GROUP BY

SELECT country, COUNT(*) FROM users GROUP BY country;

SELECT country, AVG(age)
FROM users
GROUP BY country
HAVING COUNT(*) > 100;

Order выполнения

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Важно помнить.

JOIN

INNER JOIN

SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id;

Only matching rows.

LEFT JOIN

SELECT * FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

All users, even без orders.

RIGHT / FULL OUTER

Rarely used. LEFT more common.

Multiple JOINs

SELECT *
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

Subqueries

In WHERE

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

In FROM

SELECT avg_age FROM (
    SELECT country, AVG(age) AS avg_age
    FROM users
    GROUP BY country
) sub
WHERE avg_age > 30;

Correlated

SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.amount > 100
);

CTE (Common Table Expression)

Readable alternative subqueries:

WITH active_users AS (
    SELECT * FROM users WHERE last_login > CURRENT_DATE - 30
),
their_orders AS (
    SELECT o.* FROM orders o
    JOIN active_users u ON u.id = o.user_id
)
SELECT * FROM their_orders;

Recursive

WITH RECURSIVE dates AS (
    SELECT '2026-01-01'::DATE AS d
    UNION ALL
    SELECT d + 1 FROM dates WHERE d < '2026-04-30'
)
SELECT * FROM dates;

Для иерархий, dates, sequences.

Window functions

ROW_NUMBER

SELECT user_id, product,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders;

Unique number per group.

RANK / DENSE_RANK

RANK() OVER (...)           -- Tie: same rank, skip next
DENSE_RANK() OVER (...)      -- Tie: same, no skip

LAG / LEAD

SELECT day, revenue,
       LAG(revenue) OVER (ORDER BY day) AS prev,
       revenue - LAG(revenue) OVER (ORDER BY day) AS mom
FROM daily;

Running total

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily;

Moving average

SELECT day, revenue,
       AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily;

Conditional

CASE WHEN

SELECT
    CASE
        WHEN age < 18 THEN 'minor'
        WHEN age < 65 THEN 'adult'
        ELSE 'senior'
    END AS age_group
FROM users;

String functions

UPPER(col), LOWER(col)
LENGTH(col)
SUBSTRING(col, 1, 5)  -- или SUBSTR
CONCAT(col1, col2)    -- или col1 || col2 в Postgres
TRIM(col)
REPLACE(col, 'a', 'b')

Extract from email

SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
-- Postgres
SPLIT_PART(email, '@', 2)

Date functions

CURRENT_DATE
CURRENT_TIMESTAMP
EXTRACT(YEAR FROM DATE)
EXTRACT(MONTH FROM DATE)
DATE_TRUNC('month', DATE)
AGE(date1, date2)
DATE + INTERVAL '1 day'

NULL handling

col IS NULL / col IS NOT NULL
COALESCE(col, default)      -- first non-NULL
NULLIF(col, value)           -- NULL if value

Careful:

• NULL = NULL → UNKNOWN (not TRUE)
• NOT IN (subquery with NULLs) → empty result
• Use IS NULL / NOT EXISTS

UNION

SELECT col FROM table1
UNION         -- removes duplicates, slower
SELECT col FROM table2;

SELECT col FROM table1
UNION ALL     -- keeps duplicates, faster
SELECT col FROM table2;

INSERT / UPDATE / DELETE

INSERT INTO users (name, age) VALUES ('Ivan', 30);
UPDATE users SET age = 31 WHERE id = 1;
DELETE FROM users WHERE id = 1;

Index basics

CREATE INDEX idx_name ON users(column);

Speeds up WHERE / JOIN / ORDER BY.

Common tasks

Duplicates

-- Find
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Remove (keep one)
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id) FROM users GROUP BY email
);

Top-N per group

WITH ranked AS (
    SELECT *, DENSE_RANK() OVER (PARTITION BY cat ORDER BY price DESC) AS rnk
    FROM products
)
SELECT * FROM ranked WHERE rnk <= 3;

Retention

WITH cohort AS (
    SELECT user_id, DATE_TRUNC('month', MIN(created_at)) AS cohort_m
    FROM users GROUP BY user_id
)
-- See cohort SQL article

Funnel

SELECT
    COUNT(DISTINCT CASE WHEN step = 'signup' THEN user_id END) AS s,
    COUNT(DISTINCT CASE WHEN step = 'active' THEN user_id END) AS a,
    COUNT(DISTINCT CASE WHEN step = 'paid' THEN user_id END) AS p
FROM events;

Tips

Performance

• Index WHERE / JOIN columns
• Avoid function on col (breaks index)
• LIMIT для exploration
• EXPLAIN ANALYZE

Readability

• Formatting (каждый keyword новая строка)
• Aliases meaningful
• CTE over subqueries для long queries

Debugging

• Build step by step
• SELECT before full query
• Verify counts / totals

Common mistakes

• SELECT * в production
• No JOIN condition → cross product
• NULL comparisons
• HAVING on non-aggregates

Учебные resources

• SQLBolt — interactive
• LeetCode Database — 200+ problems
• HackerRank SQL
• Карьерник — Russian tasks

Связанные темы

• 50 вопросов SQL
• Задачи SQL на собесе
• JOIN шпаргалка
• CTE шпаргалка
• Window functions шпаргалка

FAQ

SQL versions?

Similar между PostgreSQL, MySQL, SQL Server. Minor syntax diff.

Как start?

SQLBolt → easy LeetCode → medium problems → real projects.

Нужен ли SQL certification?

No. Projects / portfolio > certs.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.