SQL querySELECT
s.shot_for AS team,
ROUND(SUM(s.xg)::numeric, 2) AS total_xg,
ROUND(SUM(CASE WHEN s.play_status = 'set_piece' THEN s.xg ELSE 0 END)::numeric, 2) AS set_piece_xg,
ROUND((SUM(CASE WHEN s.play_status = 'set_piece' THEN s.xg ELSE 0 END) / NULLIF(SUM(s.xg), 0) * 100)::numeric, 1) AS set_piece_xg_pct
FROM understat_reporting.shots s
JOIN understat_reporting.match_details md ON s.match_id = md.match_id
WHERE md.league = 'EPL'
AND md.season = '2024'
AND s.shot_for ILIKE ANY(ARRAY['%Arsenal%', '%Liverpool%', '%Manchester City%', '%Chelsea%', '%Tottenham%', '%Manchester United%'])
GROUP BY s.shot_for
ORDER BY set_piece_xg_pct DESC