Условие

Genius Sport — компания, продающая B2B сервисы статистики спортивных матчей. Тестовый датасет за 2020 + 2021 годы, листы Excel:

Games (Date, MatchID, MatchName, Sport, MatchState, Region) — список матчей. MatchState ∈ {Completed, Removed}.
Invoice (Date, Customer, MatchID, PriceOfGame, MatchState) — выставленные инвойсы клиентам.
Incidents (IncidentID, MatchID, Origin, Severity) — инциденты на матчах (Internal/External, Critical/...).

Задача (типовая для Reporting Analyst): построить отчёт, отвечающий на запросы бизнеса. Вопросы (предполагаемые):

Сколько матчей продано за период, по клиентам и видам спорта?
Какова доля Removed-матчей среди инвойс-выставленных, и почему это плохо?
Какова частота инцидентов на платных матчах? Связь Internal vs External и Severity.
Какие клиенты приносят больше выручки и какие у них SLA-проблемы (доля инцидентов на их матчах)?
Динамика год-к-году (2020 → 2021).

Решение

Подход

Это reporting-задача: нужно подготовить набор связанных таблиц / визуализаций, отвечающих на вопросы. Главное:

Чистота связки Games ↔ Invoice ↔ Incidents через MatchID.
Корректные определения: «проданный матч» = есть строка в Invoice; «возвращённый» = MatchState='Removed' в Invoice; «инцидентный» = есть в Incidents.
Год-к-году — очень частый вопрос reporting; обеспечить through сравнения в одинаковых периодах.

Реализация

Подготовка (SQL)

-- Унифицированная fact-таблица на (date, customer, match_id, sport, region)
WITH games_clean AS (
  SELECT
    DATE(g.Date)                                AS match_dt,
    g.MatchID                                   AS match_id,
    g.MatchName                                 AS match_name,
    g.Sport                                     AS sport,
    g.MatchState                                AS match_state,
    g.Region                                    AS region
  FROM games g
),
invoices_clean AS (
  SELECT
    DATE(i.Date)                                AS invoice_dt,
    i.MatchID                                   AS match_id,
    i.Customer                                  AS customer,
    -- "$1.00" → 1.0
    CAST(REPLACE(i.PriceOfGame, '$', '') AS NUMERIC) AS price,
    i.MatchState                                AS invoice_state
  FROM invoice i
),
incidents_clean AS (
  SELECT
    inc.IncidentID                              AS incident_id,
    inc.MatchID                                 AS match_id,
    inc.Origin                                  AS origin,
    inc.Severity                                AS severity
  FROM incidents inc
)

Отчёт 1. Объём проданных матчей по клиентам и видам спорта

SELECT
  EXTRACT(YEAR FROM iv.invoice_dt) AS year,
  iv.customer,
  g.sport,
  COUNT(DISTINCT iv.match_id)      AS matches_invoiced,
  SUM(iv.price)                    AS revenue,
  COUNT(DISTINCT iv.match_id)
    FILTER (WHERE iv.invoice_state = 'Removed') AS removed_invoices,
  SUM(iv.price)
    FILTER (WHERE iv.invoice_state = 'Removed') AS removed_revenue
FROM invoices_clean iv
LEFT JOIN games_clean g ON g.match_id = iv.match_id
GROUP BY 1, 2, 3
ORDER BY year, revenue DESC;

Visualisation: stacked bar по customer × sport, в подсказке — выручка.

Отчёт 2. Доля Removed-инвойсов и год-к-году

SELECT
  EXTRACT(YEAR FROM invoice_dt) AS year,
  COUNT(*)                                                       AS total_invoices,
  COUNT(*) FILTER (WHERE invoice_state = 'Removed')              AS removed,
  COUNT(*) FILTER (WHERE invoice_state = 'Removed') * 1.0
    / COUNT(*)                                                   AS removed_share,
  SUM(price)                                                     AS gross_revenue,
  SUM(price) FILTER (WHERE invoice_state = 'Removed')            AS removed_revenue,
  SUM(price) - SUM(price) FILTER (WHERE invoice_state = 'Removed') AS net_revenue
FROM invoices_clean
GROUP BY 1
ORDER BY 1;

Visualisation: line-chart removed_share по годам, с горизонтальной линией бенчмарка (≤ 5% — норм, > 10% — beta-зона).

Отчёт 3. Инциденты на платных матчах

SELECT
  EXTRACT(YEAR FROM iv.invoice_dt)             AS year,
  inc.origin,
  inc.severity,
  COUNT(*)                                     AS incidents,
  COUNT(DISTINCT inc.match_id)                 AS matches_with_incidents,
  COUNT(DISTINCT iv.match_id)                  AS total_invoiced_matches,
  COUNT(DISTINCT inc.match_id) * 1.0
    / NULLIF(COUNT(DISTINCT iv.match_id), 0)   AS incident_rate
FROM invoices_clean iv
LEFT JOIN incidents_clean inc ON inc.match_id = iv.match_id
WHERE inc.incident_id IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY year, origin, severity;

Visualisation: stacked bar по (year × origin), цветом — severity. Heatmap «origin × severity» с числами инцидентов.

Отчёт 4. Клиенты — выручка vs SLA-проблемы

WITH customer_stats AS (
  SELECT
    iv.customer,
    SUM(iv.price)                                                  AS revenue,
    COUNT(DISTINCT iv.match_id)                                    AS matches,
    COUNT(DISTINCT inc.match_id) FILTER (WHERE inc.severity = 'Critical')
                                                                    AS critical_incident_matches
  FROM invoices_clean iv
  LEFT JOIN incidents_clean inc ON inc.match_id = iv.match_id
  GROUP BY iv.customer
)
SELECT
  customer,
  revenue,
  matches,
  critical_incident_matches,
  critical_incident_matches * 1.0 / matches AS critical_incident_rate
FROM customer_stats
ORDER BY revenue DESC;

Visualisation: scatter-plot «revenue» × «critical_incident_rate», размер точки — matches. Идеал — высокий revenue + низкий incident_rate.

Отчёт 5. Год-к-году

SELECT
  EXTRACT(MONTH FROM invoice_dt) AS month,
  SUM(price) FILTER (WHERE EXTRACT(YEAR FROM invoice_dt) = 2020) AS rev_2020,
  SUM(price) FILTER (WHERE EXTRACT(YEAR FROM invoice_dt) = 2021) AS rev_2021,
  (SUM(price) FILTER (WHERE EXTRACT(YEAR FROM invoice_dt) = 2021) -
   SUM(price) FILTER (WHERE EXTRACT(YEAR FROM invoice_dt) = 2020))
    / NULLIF(SUM(price) FILTER (WHERE EXTRACT(YEAR FROM invoice_dt) = 2020), 0) AS yoy_change
FROM invoices_clean
GROUP BY 1
ORDER BY 1;

Visualisation: 2 линии по месяцам (2020, 2021); barchart YoY change.

Анализ / интерпретация

Шаблон выводов для отчётности:

Выручка: 2021 год показал рост +X% YoY. Рост сосредоточен в видах спорта Football, Basketball; провал в Tennis (-Y%) — связано с сокращением турнирной программы.

Removed share: 12% инвойсов в 2020 → 8% в 2021. Снижение — позитив (меньше отказов клиентов от уже выставленных инвойсов).

Инциденты: критические инциденты на 3% инвойсированных матчей. Internal причины (наша инфраструктура) — 60% инцидентов. Это рычаг для R&D команды.

Топ-клиенты: Customer X приносит 40% revenue, но имеет 8% критических инцидентов на матчах — выше среднего. Riskconcentration. Нужно усилить SLA для этого клиента.

Подводные камни

MatchState разный в Games и Invoice. В Games — общий статус матча (Completed/Removed). В Invoice — состояние инвойса. Не путайте.
PriceOfGame как строка "$3.00". Очистка через REPLACE + CAST обязательна.
Один MatchID — много инвойсов (несколько клиентов покупают один матч). При расчёте инцидентов на матч важно: «есть ли инцидент» — да/нет, а не SUM.
Region в Games и Customer.Country. Отдельные оси, не путайте.
MatchState='Removed' в Games. Что это значит? Матч отменился. Если у такого матча в Invoice инвойсы выставлены — нужно ли возвращать деньги? Уточнять.
Severity = NULL. Может встречаться. Считайте «unknown» отдельно.
Неполные дни / месяцы в данных за 2020-2021. Год-к-году сравнивайте только закрытые периоды.
Inflation / pricing changes. Выручка может расти из-за повышения цен, не из-за объёма.
Customer опечатки (drunkenbear vs DrunkenBear). Очистите перед отчётом.

Альтернативы

Если данных много — построить DWH (BigQuery / Snowflake) с фактовой таблицей match × customer × invoice + dimension таблицами; в BI — слайс-интерфейс.
Tableau / Power BI / Looker Studio для визуализации.
dbt для управления моделями данных и тестов.

Эталонный ответ

Структура отчёта Reporting Analyst:

Executive summary — 3–5 выводов в первом абзаце.
Revenue overview: customer × sport × region.
Removed share: тренд год-к-году.
Incidents: origin × severity, incident rate per match.
Customer SLA: scatter revenue vs incident_rate, identify risk-concentration.
YoY: month-by-month сравнение.
Каждый раздел — одна таблица + один график; выводы в абзаце ниже.

Главное — связать данные через MatchID и очистить типы (price из строки, customer от опечаток); реальная reporting-работа на 50% — это data hygiene.

Genius Sport — отчётность по матчам, инвойсам и инцидентам