SQL для финансового аналитика на собесе

Q: Где практиковать?

[SQL-тренажёр](/sql-trenazher), Mode Analytics SQL challenges, open financial datasets.

Зачем SQL на собесе финаналитика

В 2026 финансовый аналитик в IT не может обходиться без SQL. Excel + ручной импорт CSV — устарело. Современный финаналитик пишет SQL для retrievals из DWH: revenue по periods, customer breakdown, variance анализ.

На собесе финаналитика SQL — обязательный live-coding раздел. Слабый ответ — «select count». Сильный — window functions, агрегации по периодам, variance анализ.

Базовые операции

SELECT, FROM, WHERE, GROUP BY — стандарт.

JOIN:

INNER JOIN для transactions + customers
LEFT JOIN когда нужны customers без orders
Multiple joins для P&L (revenue + costs + categories)

Aggregations:

SELECT 
  DATE_TRUNC('month', order_date) AS month,
  SUM(amount) AS revenue,
  COUNT(*) AS orders,
  COUNT(DISTINCT customer_id) AS customers,
  AVG(amount) AS avg_check
FROM orders
GROUP BY 1
ORDER BY 1;

Period-over-period analysis

Финансовый аналитик постоянно считает MoM / YoY / variance.

LAG для prior period:

SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS delta,
  (revenue / LAG(revenue) OVER (ORDER BY month) - 1) * 100 AS mom_growth_pct
FROM monthly_revenue;

YoY (12-month LAG):

LAG(revenue, 12) OVER (ORDER BY month) AS revenue_last_year

Running totals:

SUM(revenue) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS ytd_revenue

Moving averages (smoothing):

AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS revenue_3m_ma

Подробнее — LAG, LEAD в SQL, оконные функции.

Variance анализ (plan vs actual)

WITH actual AS (
  SELECT period, category, SUM(amount) AS actual_amount
  FROM transactions
  GROUP BY 1, 2
),
budget AS (
  SELECT period, category, budget_amount
  FROM budget_data
)
SELECT b.period, b.category,
  b.budget_amount,
  COALESCE(a.actual_amount, 0) AS actual_amount,
  COALESCE(a.actual_amount, 0) - b.budget_amount AS variance,
  (COALESCE(a.actual_amount, 0) / NULLIF(b.budget_amount, 0) - 1) * 100 AS variance_pct
FROM budget b
LEFT JOIN actual a USING (period, category)
ORDER BY ABS(variance) DESC;

P&L SQL

Aggregate revenue + costs по category:

WITH revenue AS (
  SELECT DATE_TRUNC('month', DATE) AS month,
    SUM(amount) AS total_revenue
  FROM transactions WHERE type = 'revenue'
  GROUP BY 1
),
costs AS (
  SELECT DATE_TRUNC('month', DATE) AS month,
    SUM(CASE WHEN category = 'COGS' THEN amount ELSE 0 END) AS cogs,
    SUM(CASE WHEN category = 'opex' THEN amount ELSE 0 END) AS opex,
    SUM(CASE WHEN category = 'marketing' THEN amount ELSE 0 END) AS marketing
  FROM transactions WHERE type = 'expense'
  GROUP BY 1
)
SELECT r.month,
  r.total_revenue,
  c.cogs,
  r.total_revenue - c.cogs AS gross_profit,
  c.opex,
  c.marketing,
  r.total_revenue - c.cogs - c.opex - c.marketing AS ebitda
FROM revenue r LEFT JOIN costs c USING (month)
ORDER BY 1;

Cohort revenue analysis

Cohort by signup month → revenue в каждый последующий month.

WITH cohorts AS (
  SELECT user_id, DATE_TRUNC('month', signup_date) AS cohort_month
  FROM users
),
revenue AS (
  SELECT user_id, DATE_TRUNC('month', order_date) AS revenue_month,
    SUM(amount) AS rev
  FROM orders
  GROUP BY 1, 2
)
SELECT c.cohort_month, r.revenue_month,
  SUM(r.rev) AS cohort_revenue,
  COUNT(DISTINCT r.user_id) AS active_users
FROM cohorts c JOIN revenue r USING (user_id)
GROUP BY 1, 2;

Categorization / mapping

Часто данные в DWH grain-level, нужно map к финансовым категориям.

CASE / mapping table:

SELECT 
  t.amount,
  CASE 
    WHEN t.account_code BETWEEN '4000' AND '4999' THEN 'Revenue'
    WHEN t.account_code BETWEEN '5000' AND '5999' THEN 'COGS'
    WHEN t.account_code BETWEEN '6000' AND '6999' THEN 'OpEx'
    ELSE 'Other'
  END AS finance_category
FROM transactions t;

Лучше: mapping table в database, JOIN с ней.

Типичные вопросы

«MoM growth revenue за последние 12 месяцев»

LAG(revenue) OVER (ORDER BY month). delta + delta_pct.

«YoY variance по region»

Group by region + month. LAG 12 months. Variance = current - last_year.

«Top-10 customers по revenue»

GROUP BY customer + SUM, ORDER BY total DESC LIMIT 10.

«Cash flow прогноз на следующие 3 месяца»

Historical: revenue, expenses by month. Trend extrapolation (LAG / moving avg). Можно отдельно ML — но base в SQL.

«Find users без orders за last 90 days»

LEFT JOIN orders с фильтром last 90d, WHERE order IS NULL.

Частые ошибки

DATE без timezone. Cross-region отчёты ломаются.
NULL в aggregations. SUM игнорирует, AVG может surprise.
GROUP BY без proper aggregation. Logical error.
Без partition pruning на больших таблицах. Запросы tail.
Window без partition. Для multi-region — нужен PARTITION BY region.

FAQ

Финаналитика и DWH — какой стек?

ClickHouse / Snowflake / Greenplum / BigQuery в зависимости от компании. SQL диалект ~ANSI базовый.

Excel + SQL вместе?

Yes. SQL для retrieval из DWH, Excel для модели / dashboards. Power Query bridges them.

dbt для финаналитика?

Растёт. dbt-модели для finance mart (P&L, balance sheet, ratios). Standard в 2026.

Python нужен?

Для advanced (forecasting, ML scoring) — да. Для базовой работы — SQL достаточно.

Где практиковать?

SQL-тренажёр, Mode Analytics SQL challenges, open financial datasets.

Смотрите также

Тренироваться в Telegram