{"queries":[{"id":"match_info","compiledQueryString":"select\n home_team,\n away_team,\n home_team_short,\n away_team_short,\n home_team_logo,\n away_team_logo,\n strftime(match_date, '%Y-%m-%d') as match_date,\n round,\n kick_off_time,\n stadium\nfrom scotland.mart_upcoming\nwhere match_id = cast('${inputs.match.value}' as bigint)\nlimit 1","inputQueryString":"select\n home_team,\n away_team,\n home_team_short,\n away_team_short,\n home_team_logo,\n away_team_logo,\n strftime(match_date, '%Y-%m-%d') as match_date,\n round,\n kick_off_time,\n stadium\nfrom scotland.mart_upcoming\nwhere match_id = cast('${inputs.match.value}' as bigint)\nlimit 1","compiled":false,"inline":true},{"id":"prediction","compiledQueryString":"select *\nfrom scotland.mart_upcoming_predictions\nwhere match_id = cast('${inputs.match.value}' as bigint)\nlimit 1","inputQueryString":"select *\nfrom scotland.mart_upcoming_predictions\nwhere match_id = cast('${inputs.match.value}' as bigint)\nlimit 1","compiled":false,"inline":true},{"id":"h2h_seasons","compiledQueryString":"select distinct mc.season\nfrom scotland.mart_match_card mc\njoin (select\n home_team,\n away_team,\n home_team_short,\n away_team_short,\n home_team_logo,\n away_team_logo,\n strftime(match_date, '%Y-%m-%d') as match_date,\n round,\n kick_off_time,\n stadium\nfrom scotland.mart_upcoming\nwhere match_id = cast('${inputs.match.value}' as bigint)\nlimit 1) mi\n on (mc.home_team = mi.home_team and mc.away_team = mi.away_team)\n or (mc.home_team = mi.away_team and mc.away_team = mi.home_team)\norder by mc.season desc","inputQueryString":"select distinct mc.season\nfrom scotland.mart_match_card mc\njoin ${match_info} mi\n on (mc.home_team = mi.home_team and mc.away_team = mi.away_team)\n or (mc.home_team = mi.away_team and mc.away_team = mi.home_team)\norder by mc.season desc","compiled":true,"inline":true},{"id":"h2h","compiledQueryString":"select\n mc.season,\n mc.match_date,\n mc.match_round_name as round,\n cast(mc.match_round_number as int) as round_no,\n mc.home_team_short || ' - ' || mc.away_team_short as match,\n case\n when mc.home_goals = mc.away_goals then 'Draw'\n when (case when mc.home_goals > mc.away_goals then mc.home_team else mc.away_team end) = mi.home_team\n then ''\n || (case when mc.home_goals > mc.away_goals then mc.home_team_short else mc.away_team_short end)\n || ''\n else ''\n || (case when mc.home_goals > mc.away_goals then mc.home_team_short else mc.away_team_short end)\n || ''\n end as winner,\n mc.score,\n (mc.home_goals + mc.away_goals)::int as total_goals,\n (mc.home_sog + mc.away_sog)::int as total_shots_on_goal,\n (mc.home_big_chances + mc.away_big_chances)::int as total_big_chances\nfrom scotland.mart_match_card mc\njoin (select\n home_team,\n away_team,\n home_team_short,\n away_team_short,\n home_team_logo,\n away_team_logo,\n strftime(match_date, '%Y-%m-%d') as match_date,\n round,\n kick_off_time,\n stadium\nfrom scotland.mart_upcoming\nwhere match_id = cast('${inputs.match.value}' as bigint)\nlimit 1) mi\n on (mc.home_team = mi.home_team and mc.away_team = mi.away_team)\n or (mc.home_team = mi.away_team and mc.away_team = mi.home_team)\nwhere mc.season in ${inputs.h2h_season.value}\n and mc.home_goals is not null\norder by mc.match_date desc","inputQueryString":"select\n mc.season,\n mc.match_date,\n mc.match_round_name as round,\n cast(mc.match_round_number as int) as round_no,\n mc.home_team_short || ' - ' || mc.away_team_short as match,\n case\n when mc.home_goals = mc.away_goals then 'Draw'\n when (case when mc.home_goals > mc.away_goals then mc.home_team else mc.away_team end) = mi.home_team\n then ''\n || (case when mc.home_goals > mc.away_goals then mc.home_team_short else mc.away_team_short end)\n || ''\n else ''\n || (case when mc.home_goals > mc.away_goals then mc.home_team_short else mc.away_team_short end)\n || ''\n end as winner,\n mc.score,\n (mc.home_goals + mc.away_goals)::int as total_goals,\n (mc.home_sog + mc.away_sog)::int as total_shots_on_goal,\n (mc.home_big_chances + mc.away_big_chances)::int as total_big_chances\nfrom scotland.mart_match_card mc\njoin ${match_info} mi\n on (mc.home_team = mi.home_team and mc.away_team = mi.away_team)\n or (mc.home_team = mi.away_team and mc.away_team = mi.home_team)\nwhere mc.season in ${inputs.h2h_season.value}\n and mc.home_goals is not null\norder by mc.match_date desc","compiled":true,"inline":true},{"id":"h2h_stats","compiledQueryString":"select\n sum(case when (mc.home_team = mi.home_team and mc.home_goals > mc.away_goals)\n or (mc.away_team = mi.home_team and mc.away_goals > mc.home_goals) then 1 else 0 end) as team1_wins,\n sum(case when mc.home_goals = mc.away_goals then 1 else 0 end) as draws,\n sum(case when (mc.home_team = mi.away_team and mc.home_goals > mc.away_goals)\n or (mc.away_team = mi.away_team and mc.away_goals > mc.home_goals) then 1 else 0 end) as team2_wins\nfrom scotland.mart_match_card mc\njoin (select\n home_team,\n away_team,\n home_team_short,\n away_team_short,\n home_team_logo,\n away_team_logo,\n strftime(match_date, '%Y-%m-%d') as match_date,\n round,\n kick_off_time,\n stadium\nfrom scotland.mart_upcoming\nwhere match_id = cast('${inputs.match.value}' as bigint)\nlimit 1) mi\n on (mc.home_team = mi.home_team and mc.away_team = mi.away_team)\n or (mc.home_team = mi.away_team and mc.away_team = mi.home_team)\nwhere mc.season in ${inputs.h2h_season.value}\n and mc.home_goals is not null","inputQueryString":"select\n sum(case when (mc.home_team = mi.home_team and mc.home_goals > mc.away_goals)\n or (mc.away_team = mi.home_team and mc.away_goals > mc.home_goals) then 1 else 0 end) as team1_wins,\n sum(case when mc.home_goals = mc.away_goals then 1 else 0 end) as draws,\n sum(case when (mc.home_team = mi.away_team and mc.home_goals > mc.away_goals)\n or (mc.away_team = mi.away_team and mc.away_goals > mc.home_goals) then 1 else 0 end) as team2_wins\nfrom scotland.mart_match_card mc\njoin ${match_info} mi\n on (mc.home_team = mi.home_team and mc.away_team = mi.away_team)\n or (mc.home_team = mi.away_team and mc.away_team = mi.home_team)\nwhere mc.season in ${inputs.h2h_season.value}\n and mc.home_goals is not null","compiled":true,"inline":true},{"id":"home_form","compiledQueryString":"select\n strftime(tm.match_date, '%d %b') as match_date,\n tm.opponent_team_short_name as opponent,\n tm.goals_scored as gf,\n tm.goals_conceded as ga,\n tm.result\nfrom scotland.mart_team_match tm\njoin (select\n home_team,\n away_team,\n home_team_short,\n away_team_short,\n home_team_logo,\n away_team_logo,\n strftime(match_date, '%Y-%m-%d') as match_date,\n round,\n kick_off_time,\n stadium\nfrom scotland.mart_upcoming\nwhere match_id = cast('${inputs.match.value}' as bigint)\nlimit 1) mi on tm.team_name = mi.home_team\nwhere tm.result in ('Win', 'Draw', 'Loss')\n and tm.match_date >= current_date - interval '6 months'\norder by tm.match_date desc\nlimit 5","inputQueryString":"select\n strftime(tm.match_date, '%d %b') as match_date,\n tm.opponent_team_short_name as opponent,\n tm.goals_scored as gf,\n tm.goals_conceded as ga,\n tm.result\nfrom scotland.mart_team_match tm\njoin ${match_info} mi on tm.team_name = mi.home_team\nwhere tm.result in ('Win', 'Draw', 'Loss')\n and tm.match_date >= current_date - interval '6 months'\norder by tm.match_date desc\nlimit 5","compiled":true,"inline":true},{"id":"away_form","compiledQueryString":"select\n strftime(tm.match_date, '%d %b') as match_date,\n tm.opponent_team_short_name as opponent,\n tm.goals_scored as gf,\n tm.goals_conceded as ga,\n tm.result\nfrom scotland.mart_team_match tm\njoin (select\n home_team,\n away_team,\n home_team_short,\n away_team_short,\n home_team_logo,\n away_team_logo,\n strftime(match_date, '%Y-%m-%d') as match_date,\n round,\n kick_off_time,\n stadium\nfrom scotland.mart_upcoming\nwhere match_id = cast('${inputs.match.value}' as bigint)\nlimit 1) mi on tm.team_name = mi.away_team\nwhere tm.result in ('Win', 'Draw', 'Loss')\n and tm.match_date >= current_date - interval '6 months'\norder by tm.match_date desc\nlimit 5","inputQueryString":"select\n strftime(tm.match_date, '%d %b') as match_date,\n tm.opponent_team_short_name as opponent,\n tm.goals_scored as gf,\n tm.goals_conceded as ga,\n tm.result\nfrom scotland.mart_team_match tm\njoin ${match_info} mi on tm.team_name = mi.away_team\nwhere tm.result in ('Win', 'Draw', 'Loss')\n and tm.match_date >= current_date - interval '6 months'\norder by tm.match_date desc\nlimit 5","compiled":true,"inline":true},{"id":"last_updated","compiledQueryString":"select * from scotland.last_updated","inputQueryString":"select * from scotland.last_updated","compiled":false,"inline":true}]}

Match Preview