SQL вопросы на собесе в Тинькофф

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

Тинькофф (Т-Банк) — top employer analysts в Russia. Tech-heavy banking. Fintech-focused SQL questions.

High bar SQL — prepare properly.

Процесс

• 60-90 min technical
• 2-4 tasks
• Live coding
• Verbal explanation

Стек

• PostgreSQL, ClickHouse
• Python, pandas
• Spark для big data

Типы задач

1. Financial aggregations

• Balances, transactions, interest
• Running balances
• Rolling windows

2. Customer analytics

• Cohort retention
• Churn analysis
• LTV

3. Fraud

• Anomaly detection
• Velocity rules
• Network patterns

4. Credit

• Portfolio metrics
• Risk calculations
• NPL

Пример 1: Transaction velocity

«Flag transactions > 10 per минуте для same card».

SELECT
    transaction_id,
    card_id,
    created_at
FROM (
    SELECT
        *,
        COUNT(*) OVER (PARTITION BY card_id ORDER BY created_at
                       RANGE BETWEEN INTERVAL '1 minute' PRECEDING AND CURRENT ROW) AS tx_last_minute
    FROM transactions
) t
WHERE tx_last_minute > 10;

Advanced windows — RANGE frames.

Пример 2: Active customers

«Customers who made ≥ 1 transaction в each of last 3 months».

WITH monthly_activity AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', created_at) AS month,
        COUNT(*) AS tx_count
    FROM transactions
    WHERE created_at >= CURRENT_DATE - 90
    GROUP BY 1, 2
)
SELECT customer_id
FROM monthly_activity
GROUP BY customer_id
HAVING COUNT(DISTINCT month) = 3;

Пример 3: Average balance

«Avg month-end balance for each customer за last 12 months».

WITH month_end_balance AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', DATE) AS month,
        LAST_VALUE(balance) OVER (
            PARTITION BY customer_id, DATE_TRUNC('month', DATE)
            ORDER BY DATE
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS month_end_balance
    FROM daily_balances
    WHERE DATE >= CURRENT_DATE - 365
)
SELECT customer_id, AVG(month_end_balance) AS avg_monthly_balance
FROM (SELECT DISTINCT customer_id, month, month_end_balance FROM month_end_balance) t
GROUP BY customer_id;

Пример 4: NPL rate

«Non-performing loans (> 90 days past due) percentage».

SELECT
    DATE_TRUNC('month', DATE) AS month,
    COUNT(CASE WHEN days_past_due > 90 THEN 1 END) * 1.0 /
        COUNT(*) AS npl_rate
FROM loan_portfolio_snapshots
WHERE DATE >= CURRENT_DATE - 365
    AND status = 'active'
GROUP BY 1;

Пример 5: Cohort LTV

«Avg revenue per cohort month, 12 months tenure».

WITH cohorts AS (
    SELECT customer_id, DATE_TRUNC('month', signup_date) AS cohort_month
    FROM customers
),
revenue AS (
    SELECT
        c.cohort_month,
        c.customer_id,
        SUM(r.amount) AS total_revenue
    FROM cohorts c
    LEFT JOIN revenue_events r ON r.customer_id = c.customer_id
        AND r.created_at <= c.cohort_month + INTERVAL '12 months'
    GROUP BY 1, 2
)
SELECT
    cohort_month,
    AVG(total_revenue) AS avg_12m_ltv
FROM revenue
GROUP BY cohort_month
ORDER BY cohort_month;

Пример 6: Fraud pattern

«Card used in 2 countries в 1 hour».

WITH geo_jumps AS (
    SELECT
        card_id,
        created_at,
        country,
        LAG(country) OVER (PARTITION BY card_id ORDER BY created_at) AS prev_country,
        LAG(created_at) OVER (PARTITION BY card_id ORDER BY created_at) AS prev_time
    FROM transactions
)
SELECT *
FROM geo_jumps
WHERE country != prev_country
    AND EXTRACT(EPOCH FROM (created_at - prev_time)) / 3600 < 1;

Geographic anomaly.

Пример 7: Time-weighted metric

«Weighted avg interest rate (by balance) portfolio».

SELECT
    SUM(balance * interest_rate) / SUM(balance) AS weighted_avg_rate
FROM current_loans
WHERE status = 'active';

Simple weighted aggregation.

Python часть

Тинькофф часто + Python:

Example

«Load transactions CSV. Find outlier amounts using IQR».

import pandas as pd

df = pd.read_csv('transactions.csv')

q1 = df['amount'].quantile(0.25)
q3 = df['amount'].quantile(0.75)
iqr = q3 - q1

lower = q1 - 1.5 * iqr
upper = q3 + 1.5 * iqr

outliers = df[(df['amount'] < lower) | (df['amount'] > upper)]
print(outliers)

Example 2

«Retention D30 cohort analysis через pandas».

# Cohort month
df['signup_month'] = df['signup_date'].dt.to_period('M')

# Activity months
activity = pd.merge(
    df[['user_id', 'signup_month']],
    events_df,
    on='user_id'
)

activity['months_since'] = (
    activity['event_date'].dt.to_period('M') - activity['signup_month']
).apply(lambda x: x.n)

# Cohort matrix
cohort_matrix = activity.pivot_table(
    index='signup_month',
    columns='months_since',
    values='user_id',
    aggfunc='nunique'
)

# Cohort sizes
sizes = df.groupby('signup_month')['user_id'].nunique()

# Retention
retention = cohort_matrix.divide(sizes, axis=0) * 100
print(retention)

Case questions

Credit scoring

Build model predict default:

• Features
• Model choice
• Evaluation (Gini, KS)
• Monitoring

Churn credit card

Predict card users leaving:

• Signals
• Interventions

Fraud system

Real-time fraud detection setup:

• Rules + ML
• Threshold tuning
• False positive management

Specifics Tinkoff

Culture

• Data-driven
• Ship fast
• Ownership

Tech stack

Modern. ClickHouse, Python, Airflow, dbt-adjacent.

Scope

Personal finance, business, investments. Wide domain.

Преimportant skills

SQL strong

Window functions daily. Optimization. ClickHouse.

Python solid

pandas fluently. scipy / statsmodels для stats. sklearn / xgboost.

Statistics

A/B testing rigorous. Credit scoring math.

Business

Banking basics. Products understanding.

Зарплаты

• Junior: 130-180k
• Middle: 200-300k
• Senior: 300-450k
• Lead: 450-700k

Plus bonuses, RSU (if applicable).

Подготовка

1. SQL daily practice
2. Python / pandas brush up
3. Statistics refresh
4. Read Tinkoff blog / cases
5. Mock interview

Pro tips

Live coding

Share screen. Clean style.

Think aloud

Verbal reasoning. Major factor.

Clarify

Ambiguous problem — ask.

Test

Manually trace через simple data.

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

• Собеседование в Тинькофф
• Как пройти в финтех
• SQL вопросы в Yandex
• Подготовка SQL 7 дней
• ClickHouse шпаргалка

FAQ

SQL middle или senior уровень?

Middle-strong minimum. Senior preferred.

ClickHouse обязательно?

Yes для most teams.

Russian или English?

Russian обычно.

---

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