SELECT p.name, fps.role_short, fps.fantasy_average, fps.matches_with_vote,
fps.goals, fps.assists
FROM fc_player_seasons fps
JOIN players p ON fps.player_id = p.id
WHERE fps.season_id = '2024' AND fps.matches_with_vote >= 10
ORDER BY fps.fantasy_average DESC
LIMIT 10;
SELECT p.name, us.goals, us.xG,
ROUND(us.goals - us.xG, 2) AS overperformance,
us.games
FROM us_players us
JOIN players p ON us.player_id = p.id
WHERE us.season_id = '2024' AND us.games >= 15
ORDER BY overperformance DESC
LIMIT 10;
Trend quotazioni di un giocatore nella stagione
SELECT fqh.match_day, fqh.quotation_classic, fqh.quotation_mantra
FROM fc_quotation_history fqh
JOIN fc_player_seasons fps ON fqh.player_season_id = fps.id
JOIN players p ON fps.player_id = p.id
WHERE p.name LIKE '%Lookman%' AND fps.season_id = '2024'
ORDER BY fqh.match_day;
Storico infortuni di un giocatore con tipo
SELECT p.name, ct.name AS infortunio, ct.type, tpi.from_date, tpi.until_date,
tpi.days, tpi.games_missed
FROM tm_player_injuries tpi
JOIN players p ON tpi.player_id = p.id
JOIN condition_types ct ON tpi.condition_id = ct.id
WHERE p.name LIKE '%Chiesa%'
ORDER BY tpi.from_date DESC;
Evoluzione del valore di mercato di un giocatore
SELECT p.name, tmv.date, tmv.market_value, tmv.age, c.name AS club
FROM tm_market_value tmv
JOIN players p ON tmv.player_id = p.id
LEFT JOIN clubs c ON tmv.club_id = c.id
WHERE p.name LIKE '%Vlahovic%'
ORDER BY tmv.date;
Squadre con pressing più aggressivo (PPDA basso = più pressing)
SELECT c.name,
ROUND(AVG(uth.ppda_att / NULLIF(uth.ppda_def, 0)), 2) AS ppda_medio,
COUNT(*) AS partite
FROM us_team_history uth
JOIN clubs c ON uth.club_id = c.id
WHERE uth.season_id = '2024'
GROUP BY c.id
ORDER BY ppda_medio ASC
LIMIT 10;
Migliori tiratori dalla distanza (fuori area)
SELECT p.name, ss.goals_from_outside_the_box, ss.total_shots,
ss.goal_conversion_percentage
FROM sofascore ss
JOIN players p ON ss.player_id = p.id
WHERE ss.season_id = '2024' AND ss.goals_from_outside_the_box > 0
ORDER BY ss.goals_from_outside_the_box DESC
LIMIT 10;
Portieri: clean sheet e parate
SELECT p.name, ss.clean_sheet, ss.saves, ss.penalty_save,
ss.goals_conceded_inside_the_box, ss.goals_conceded_outside_the_box,
fps.fantasy_average
FROM sofascore ss
JOIN players p ON ss.player_id = p.id
JOIN fc_player_seasons fps ON p.id = fps.player_id AND fps.season_id = ss.season_id
WHERE ss.season_id = '2024' AND fps.role_short = 'P' AND ss.appearances >= 10
ORDER BY fps.fantasy_average DESC;
Stats di un giocatore per posizione (versatilità)
SELECT p.name, usp.position, usp.games, usp.goals, usp.xG, usp.assists, usp.xA
FROM us_player_stats_by_position usp
JOIN players p ON usp.player_id = p.id
WHERE p.name LIKE '%Calhanoglu%' AND usp.season_id = '2024'
ORDER BY usp.games DESC;