Kirubel-Kibru-Yaltopia
89440985f1
Some checks failed
Deploy to Cloudflare Workers / deploy (push) Has been cancelled
106 lines
3.6 KiB
SQL
106 lines
3.6 KiB
SQL
-- Standings view (league mode)
|
|
CREATE OR REPLACE VIEW competition_standings AS
|
|
SELECT
|
|
t.competition_id,
|
|
t.id AS team_id,
|
|
t.name AS team_name,
|
|
t.logo_path,
|
|
COUNT(m.id) FILTER (WHERE m.status = 'completed') AS played,
|
|
COUNT(m.id) FILTER (
|
|
WHERE m.status = 'completed'
|
|
AND (
|
|
(m.home_team_id = t.id AND m.home_score > m.away_score)
|
|
OR (m.away_team_id = t.id AND m.away_score > m.home_score)
|
|
)
|
|
) AS won,
|
|
COUNT(m.id) FILTER (
|
|
WHERE m.status = 'completed' AND m.home_score = m.away_score
|
|
) AS drawn,
|
|
COUNT(m.id) FILTER (
|
|
WHERE m.status = 'completed'
|
|
AND (
|
|
(m.home_team_id = t.id AND m.home_score < m.away_score)
|
|
OR (m.away_team_id = t.id AND m.away_score < m.home_score)
|
|
)
|
|
) AS lost,
|
|
COALESCE(SUM(
|
|
CASE
|
|
WHEN m.status = 'completed' AND m.home_team_id = t.id THEN m.home_score
|
|
WHEN m.status = 'completed' AND m.away_team_id = t.id THEN m.away_score
|
|
ELSE 0
|
|
END
|
|
), 0)::INT AS goals_for,
|
|
COALESCE(SUM(
|
|
CASE
|
|
WHEN m.status = 'completed' AND m.home_team_id = t.id THEN m.away_score
|
|
WHEN m.status = 'completed' AND m.away_team_id = t.id THEN m.home_score
|
|
ELSE 0
|
|
END
|
|
), 0)::INT AS goals_against,
|
|
COALESCE(SUM(
|
|
CASE
|
|
WHEN m.status = 'completed' AND m.home_team_id = t.id THEN m.home_score - m.away_score
|
|
WHEN m.status = 'completed' AND m.away_team_id = t.id THEN m.away_score - m.home_score
|
|
ELSE 0
|
|
END
|
|
), 0)::INT AS goal_difference,
|
|
COALESCE(SUM(
|
|
CASE
|
|
WHEN m.status <> 'completed' THEN 0
|
|
WHEN m.home_score = m.away_score THEN 1
|
|
WHEN m.home_team_id = t.id AND m.home_score > m.away_score THEN 3
|
|
WHEN m.away_team_id = t.id AND m.away_score > m.home_score THEN 3
|
|
ELSE 0
|
|
END
|
|
), 0)::INT AS points
|
|
FROM teams t
|
|
LEFT JOIN matches m ON m.competition_id = t.competition_id
|
|
AND (m.home_team_id = t.id OR m.away_team_id = t.id)
|
|
GROUP BY t.competition_id, t.id, t.name, t.logo_path;
|
|
|
|
-- Player competition stats
|
|
CREATE OR REPLACE VIEW player_competition_stats AS
|
|
SELECT
|
|
mps.player_id,
|
|
p.display_name AS player_name,
|
|
m.competition_id,
|
|
mps.team_id,
|
|
COUNT(DISTINCT mps.match_id) AS appearances,
|
|
COALESCE(SUM(mps.goals), 0)::INT AS goals,
|
|
COALESCE(SUM(mps.assists), 0)::INT AS assists
|
|
FROM match_player_stats mps
|
|
JOIN matches m ON m.id = mps.match_id AND m.status = 'completed'
|
|
JOIN players p ON p.id = mps.player_id
|
|
GROUP BY mps.player_id, p.display_name, m.competition_id, mps.team_id;
|
|
|
|
-- Team match results for charts
|
|
CREATE OR REPLACE VIEW team_match_results AS
|
|
SELECT
|
|
t.id AS team_id,
|
|
t.competition_id,
|
|
m.id AS match_id,
|
|
m.matchday,
|
|
m.scheduled_at,
|
|
CASE WHEN m.home_team_id = t.id THEN ht.name ELSE at.name END AS opponent_name,
|
|
CASE WHEN m.home_team_id = t.id THEN at.logo_path ELSE ht.logo_path END AS opponent_logo,
|
|
CASE WHEN m.home_team_id = t.id THEN m.home_score ELSE m.away_score END AS goals_for,
|
|
CASE WHEN m.home_team_id = t.id THEN m.away_score ELSE m.home_score END AS goals_against,
|
|
CASE
|
|
WHEN m.home_score = m.away_score THEN 'D'
|
|
WHEN (m.home_team_id = t.id AND m.home_score > m.away_score)
|
|
OR (m.away_team_id = t.id AND m.away_score > m.home_score) THEN 'W'
|
|
ELSE 'L'
|
|
END AS result,
|
|
CASE
|
|
WHEN m.home_score = m.away_score THEN 1
|
|
WHEN (m.home_team_id = t.id AND m.home_score > m.away_score)
|
|
OR (m.away_team_id = t.id AND m.away_score > m.home_score) THEN 3
|
|
ELSE 0
|
|
END AS points_earned
|
|
FROM teams t
|
|
JOIN matches m ON m.competition_id = t.competition_id
|
|
AND m.status = 'completed'
|
|
AND (m.home_team_id = t.id OR m.away_team_id = t.id)
|
|
JOIN teams ht ON ht.id = m.home_team_id
|
|
JOIN teams at ON at.id = m.away_team_id;
|