エンジニアが新婦のために結婚式にITで全力で貢献しようとした話【連載第13回】2,628件のトランザクションが語る結婚式の物語

イベントシステムのデータ分析SQL

ゲームタイプ別トランザクション集計

SELECT game_type,
  COUNT(*) as count,
  SUM(amount) as total_amount,
  AVG(amount)::int as avg_amount
FROM transactions
GROUP BY game_type
ORDER BY count DESC;

チップ分布のヒストグラム

SELECT
  CASE
    WHEN current_chips < 500 THEN '0-499'
    WHEN current_chips < 2000 THEN '1,000-1,999'
    WHEN current_chips < 5000 THEN '2,000-4,999'
    WHEN current_chips < 10000 THEN '5,000-9,999'
    WHEN current_chips < 50000 THEN '10,000-49,999'
    ELSE '50,000+'
  END as range,
  COUNT(*) as count
FROM profiles
WHERE team_color IS NOT NULL
GROUP BY 1 ORDER BY MIN(current_chips);

大予想のオッズ分布分析

SELECT m.title, b.option_id,
  COUNT(*) as bettors,
  SUM(b.amount) as total_bet,
  ROUND(SUM(b.amount) * 100.0 / m.total_pool, 1) as pct
FROM coliseum_bets b
JOIN coliseum_matches m ON b.match_id = m.id
WHERE m.status = 'finished'
GROUP BY m.title, b.option_id, m.total_pool
ORDER BY m.title, b.option_id;

チーム別平均チップ

SELECT team_color,
  COUNT(*) as members,
  AVG(current_chips)::int as avg_chips,
  SUM(current_chips) as total_chips
FROM profiles
WHERE team_color IS NOT NULL
GROUP BY team_color
ORDER BY avg_chips DESC;

所属別格差分析

SELECT affiliation,
  COUNT(*) as members,
  AVG(current_chips)::int as avg_chips
FROM profiles
WHERE team_color IS NOT NULL
GROUP BY affiliation
ORDER BY avg_chips DESC;

この記事の関連情報・背景解説はブログでも公開しています。
エンジニアが新婦のために結婚式にITで全力で貢献しようとした話【連載第13回】2,628件のトランザクションが語る結婚式の物語 - Secure Auto Lab