Python Coding & SQL — DE Interview PrepPython Coding и SQL — Подготовка к DE-интервью

Comprehensions vs generatorsComprehensions vs генераторы

# list comp: materializes everything (memory)
squares = [x*x for x in rng if x % 2]
# generator expr: lazy, O(1) memory, single-pass
gen = (x*x for x in rng if x % 2)
total = sum(x*x for x in rng)  # no temp list

# dict / set comps
idx = {row.id: row for row in rows}
seen = {r.key for r in rows}

Generators & pipelines (lazy)Генераторы и пайплайны (ленивые)

def read_lines(path):
    with open(path) as f:
        for line in f:          # file iterates line-by-line
            yield line.rstrip("\n")

# chain lazy steps; nothing runs until consumed
rows   = read_lines("big.log")
parsed = (parse(r) for r in rows)
valid  = (p for p in parsed if p)
for rec in valid:           # streams through pipeline
    handle(rec)

collections toolkitИнструментарий collections

from collections import defaultdict, Counter, deque

groups = defaultdict(list)
for r in rows:
    groups[r.key].append(r)        # no KeyError

freq = Counter(words)              # O(n)
freq.most_common(10)              # top-K
freq.update(more_words)

q = deque(maxlen=5)              # O(1) ends; sliding window

itertools (lazy combinators)itertools (ленивые комбинаторы)

from itertools import (groupby, islice, chain,
    accumulate, tee, count, takewhile)

# groupby needs SORTED input by the key!
for k, grp in groupby(sorted(rows, key=kf), key=kf):
    process(k, list(grp))

islice(stream, 0, 100)         # first 100, lazily
chain(a, b, c)                  # flatten iterables
accumulate(nums)                # running totals

CSV — use the module, not splitCSV — используй модуль, не split

import csv
with open("data.csv", newline="") as f:
    for row in csv.DictReader(f):   # dict per row
        print(row["ts"], row["value"])

with open("out.csv", "w", newline="") as f:
    w = csv.DictWriter(f, fieldnames=cols)
    w.writeheader(); w.writerows(records)

JSON & JSON-LinesJSON и JSON-Lines

import json
obj = json.loads(s)                 # str -> dict
s   = json.dumps(obj, default=str)  # dates etc.

# JSONL: one JSON object per line (streamable)
with open("events.jsonl") as f:
    for line in f:
        rec = json.loads(line)      # O(1) memory
        yield_event(rec)

Parquet (columnar)Parquet (колоночный)

import pyarrow.parquet as pq
table = pq.read_table("f.parquet",
    columns=["ts", "val"])      # projection pushdown

# row-group streaming for big files
pf = pq.ParquetFile("big.parquet")
for batch in pf.iter_batches(100_000):
    df = batch.to_pandas()
    process(df)

Regex for unstructured textRegex для неструктурированного текста

import re
# Apache-ish log: ip - - [ts] "GET /x" 200 1234
pat = re.compile(
  r'(?P<ip>\S+).*\[(?P<ts>[^\]]+)\]'
  r'\s+"(?P<method>\S+)\s+(?P<path>\S+)'
  r'[^"]*"\s+(?P<code>\d+)\s+(?P<bytes>\d+)')
m = pat.search(line)
if m: rec = m.groupdict()

groupby / merge / vectorizegroupby / merge / векторизация

import pandas as pd
g = (df.groupby("key")
       .agg(n=("val","size"),
            avg=("val","mean")))
m = a.merge(b, on="id", how="left",
            validate="1:1")        # catches dup keys
# vectorized, no python loop
df["z"] = (df.v - df.v.mean()) / df.v.std()

Memory disciplineДисциплина по памяти

df = pd.read_csv("f.csv",
    usecols=["ts","sym","px"],
    dtype={"sym":"category","px":"float32"},
    parse_dates=["ts"])
# chunked aggregation of a file too big for RAM
acc = Counter()
for chunk in pd.read_csv("huge.csv",
        chunksize=500_000):
    acc.update(chunk.groupby("k").size()
                    .to_dict())

Time-series in pandasВременные ряды в pandas

df = df.set_index("ts").sort_index()
daily = df["val"].resample("1D").mean()  # bucket
filled = daily.asfreq("1D").ffill()        # gap-fill
roll = df["val"].rolling("7D").mean()      # running avg
ret  = df["px"].pct_change()              # returns

pandas vs pure Python — when?pandas vs чистый Python — когда?

• pandas: tabular analytics, joins, group aggregations, time-series math; fits in memory.
• Pure Python / generators: streaming a file bigger than RAM, irregular parsing, single-pass aggregation, no heavy dep wanted.
• DB / SQL: data already in a warehouse, or joins/aggregations that should be pushed down.

• pandas: табличная аналитика, джойны, групповые агрегации, математика временных рядов; помещается в памяти.
• Чистый Python / генераторы: поток файла больше RAM, нерегулярный парсинг, агрегация за один проход, не нужна тяжёлая зависимость.
• БД / SQL: данные уже в хранилище или джойны/агрегации, которые должны быть pushed down.

Error handling & retriesОбработка ошибок и ретраи

import time
def with_retry(fn, tries=3, base=0.5):
    for i in range(tries):
        try:
            return fn()
        except (TimeoutError, ConnectionError) as e:
            if i == tries - 1:
                raise
            time.sleep(base * 2**i)   # expo backoff

Pure functions + dependency injectionЧистые функции + dependency injection

# BAD: hidden I/O, hard to test
def total():
    return sum(r.v for r in db.query())

# GOOD: pure core, I/O at the edges
def total(rows):           # deterministic, testable
    return sum(r.v for r in rows)
total(db.query())             # inject dependency

Typing & clean signaturesТипизация и чистые сигнатуры

from typing import Iterable, Iterator
from dataclasses import dataclass

@dataclass(frozen=True)
class Trade:
    ts: int; sym: str; px: float

def clean(rows: Iterable[dict]) -> Iterator[Trade]:
    for r in rows:
        yield Trade(int(r["ts"]), r["sym"],
                    float(r["px"]))

Edge cases checklist (say these aloud)Чеклист граничных случаев (проговаривай вслух)

• Empty input / single element.
• Duplicates & ties (define tie-break explicitly).
• Malformed / missing fields → skip vs raise (decide & state).
• Nulls / NaN / empty strings vs zero.
• Unsorted input when logic assumes order.
• Timezones & mixed timestamp formats.
• Very large input → does it fit in memory?

• Пустой ввод / один элемент.
• Дубликаты и равенства (определи tie-break явно).
• Кривые / отсутствующие поля → пропустить или выбросить ошибку (реши и скажи).
• Nulls / NaN / пустые строки против нуля.
• Несортированный ввод, когда логика полагается на порядок.
• Таймзоны и смешанные форматы меток времени.
• Очень большой ввод → влезает ли в память?

Window functionsОконные функции

SELECT
  user_id,
  event_ts,
  ROW_NUMBER() OVER (PARTITION BY user_id
                     ORDER BY event_ts DESC) AS rn,
  RANK()       OVER (ORDER BY amount DESC) AS rnk,
  LAG(amount)  OVER (PARTITION BY user_id
                     ORDER BY event_ts) AS prev_amt,
  SUM(amount)  OVER (PARTITION BY user_id
                     ORDER BY event_ts
                     ROWS BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW) AS running_total
FROM events;

Window frames (the subtle part)Оконные фреймы (тонкая часть)

-- default frame WITH ORDER BY is RANGE
-- UNBOUNDED PRECEDING .. CURRENT ROW
-- which makes SUM a running total (good)
-- but ties at same ORDER BY value collapse!
AVG(px) OVER (ORDER BY ts
              ROWS BETWEEN 6 PRECEDING
                   AND CURRENT ROW)  -- 7-row MA

Dedup with ROW_NUMBER (keep latest)Дедуп через ROW_NUMBER (оставить последние)

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY id
      ORDER BY updated_at DESC) AS rn
  FROM records)
SELECT * EXCEPT(rn)
FROM ranked
WHERE rn = 1;

Joins: anti & semiДжойны: анти и полу

-- ANTI JOIN: rows in A with NO match in B
SELECT a.*
FROM a LEFT JOIN b ON a.id = b.id
WHERE b.id IS NULL;

-- SEMI JOIN: rows in A that DO match (no dup blowup)
SELECT a.*
FROM a
WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id);

Gaps-and-islandsПробелы-и-острова

-- group consecutive "active" days into islands
WITH flagged AS (
  SELECT user_id, d,
    ROW_NUMBER() OVER (PARTITION BY user_id
                       ORDER BY d) AS rn
  FROM active_days)
SELECT user_id,
       MIN(d) AS start_d,
       MAX(d) AS end_d,
       COUNT(*) AS streak
FROM flagged
GROUP BY user_id,
         d - CAST(rn AS integer)   -- date minus rownum is constant per island

Sessionization (30-min gap = new session)Сессионизация (30-минутный пробел = новая сессия)

WITH gaps AS (
  SELECT *,
    CASE WHEN event_ts - LAG(event_ts) OVER (
           PARTITION BY user_id ORDER BY event_ts)
         > INTERVAL '30' MINUTE
         THEN 1 ELSE 0 END AS is_new
  FROM events),
sess AS (
  SELECT *,
    SUM(is_new) OVER (PARTITION BY user_id
        ORDER BY event_ts) AS session_id
  FROM gaps)
SELECT user_id, session_id,
       MIN(event_ts), MAX(event_ts), COUNT(*)
FROM sess GROUP BY user_id, session_id;

Resampling / gap-filling a seriesРесамплинг / заполнение пробелов ряда

-- dense calendar LEFT JOINed to sparse data,
-- then carry last value forward (LOCF)
WITH cal AS (
  SELECT d FROM UNNEST(SEQUENCE(
     DATE '2024-01-01', DATE '2024-12-31',
     INTERVAL '1' DAY)) AS t(d)),
joined AS (
  SELECT cal.d, series.val
  FROM cal LEFT JOIN series ON cal.d = series.d)
SELECT d,
  COALESCE(val, LAST_VALUE(val) IGNORE NULLS OVER (
     ORDER BY d ROWS UNBOUNDED PRECEDING)) AS val
FROM joined;

Pivot & recursive CTEPivot и рекурсивный CTE

-- conditional aggregation = portable pivot
SELECT region,
  SUM(CASE WHEN yr=2023 THEN rev END) AS y2023,
  SUM(CASE WHEN yr=2024 THEN rev END) AS y2024
FROM sales GROUP BY region;

-- recursive CTE: generate a date spine / walk a tree
WITH RECURSIVE dates(d) AS (
  SELECT DATE '2024-01-01'
  UNION ALL
  SELECT d + INTERVAL '1' DAY FROM dates
  WHERE d < DATE '2024-12-31')
SELECT * FROM dates;

How to run the 60 minutesКак провести 60 минут

1. Clarify input format, size, ordering, duplicates, edge cases — before coding.
2. State approach + big-O out loud; get a nod.
3. Code cleanly: good names, small pure functions, handle empties.
4. Walk a small example to verify; mention test cases you'd add.
5. Discuss scale: "if this didn't fit in memory I'd stream / push to SQL".

1. Уточни формат ввода, размер, упорядочивание, дубликаты, граничные случаи — до кодирования.
2. Сформулируй подход + big-O вслух; получи кивок.
3. Кодируй чисто: хорошие имена, маленькие чистые функции, обрабатывай пустоты.
4. Пройди малый пример для проверки; упомяни тест-кейсы, которые бы добавил.
5. Обсуди масштаб: «если бы это не влезало в память, я бы стримил / пушил в SQL».

Python ↔ SQL equivalences (say these)Python ↔ SQL эквиваленции (проговаривай это)

Craftsmanship signals interviewers look forСигналы мастерства, которые ищут интервьюеры

• Correctness first; edge cases handled, not hand-waved.
• Readable: meaningful names, no clever one-liners that obscure.
• Right data structure for the complexity you claim.
• Pure functions / I/O at edges → testable.
• You proactively name trade-offs and limitations.
• You'd add types + unit tests in real code.

• Корректность прежде всего; граничные случаи обработаны, не отмахнуты.
• Читаемо: значимые имена, нет умных однострочников, затемняющих суть.
• Правильная структура данных под заявленную сложность.
• Чистые функции / I/O на границах → тестируемо.
• Ты активно называешь компромиссы и ограничения.
• Ты бы добавил типы + юнит-тесты в реальном коде.