{"queries":[{"id":"season_types","compiledQueryString":"-- Defines the shared regular season and postseason filter options.\n\nSELECT 'REG' AS value, 'Regular Season' AS label, 1 AS sort_order\nUNION ALL\nSELECT 'ACC', 'ACC Post Season', 2\nUNION ALL\nSELECT 'NCAA', 'NCAA Post Season', 3\nORDER BY sort_order","inputQueryString":"-- Defines the shared regular season and postseason filter options.\n\nSELECT 'REG' AS value, 'Regular Season' AS label, 1 AS sort_order\nUNION ALL\nSELECT 'ACC', 'ACC Post Season', 2\nUNION ALL\nSELECT 'NCAA', 'NCAA Post Season', 3\nORDER BY sort_order","compiled":false,"inline":false},{"id":"latest_game","compiledQueryString":"-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1","inputQueryString":"-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1","compiled":false,"inline":false},{"id":"seasons","compiledQueryString":"-- Builds season filter options for the game snapshot page, plus an all-seasons choice.\n\nSELECT label, value, sort_order\nFROM (\n SELECT DISTINCT\n CAST(Season AS VARCHAR) AS label,\n CAST(Season AS VARCHAR) AS value,\n 9999 - CAST(Season AS INTEGER) AS sort_order\n FROM SPORTS_INFO_DW_REPORTING.venue_game_data\n\n UNION ALL\n\n SELECT 'All Seasons', 'All Seasons', 9999\n)\nORDER BY sort_order","inputQueryString":"-- Builds season filter options for the game snapshot page, plus an all-seasons choice.\n\nSELECT label, value, sort_order\nFROM (\n SELECT DISTINCT\n CAST(Season AS VARCHAR) AS label,\n CAST(Season AS VARCHAR) AS value,\n 9999 - CAST(Season AS INTEGER) AS sort_order\n FROM SPORTS_INFO_DW_REPORTING.venue_game_data\n\n UNION ALL\n\n SELECT 'All Seasons', 'All Seasons', 9999\n)\nORDER BY sort_order","compiled":false,"inline":false},{"id":"season_type_options","compiledQueryString":"-- Builds season-type filter options for the game snapshot page, including all postseason.\n\nSELECT 'All' AS value, 'All' AS label, 0 AS sort_order\nUNION ALL\nSELECT 'POST', 'All Postseason', 1\nUNION ALL\nSELECT value, label, sort_order + 1\nFROM (-- Defines the shared regular season and postseason filter options.\n\nSELECT 'REG' AS value, 'Regular Season' AS label, 1 AS sort_order\nUNION ALL\nSELECT 'ACC', 'ACC Post Season', 2\nUNION ALL\nSELECT 'NCAA', 'NCAA Post Season', 3\nORDER BY sort_order)\nORDER BY sort_order","inputQueryString":"-- Builds season-type filter options for the game snapshot page, including all postseason.\n\nSELECT 'All' AS value, 'All' AS label, 0 AS sort_order\nUNION ALL\nSELECT 'POST', 'All Postseason', 1\nUNION ALL\nSELECT value, label, sort_order + 1\nFROM ${season_types}\nORDER BY sort_order","compiled":true,"inline":false},{"id":"games_for_snapshot","compiledQueryString":"-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC","inputQueryString":"-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC","compiled":false,"inline":false},{"id":"selected_game","compiledQueryString":"-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID","inputQueryString":"-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM ${games_for_snapshot} g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM ${games_for_snapshot} LIMIT 1),\n (SELECT GameID FROM ${latest_game} LIMIT 1)\n) AS GameID","compiled":true,"inline":false},{"id":"selected_game_meta","compiledQueryString":"-- Fetches venue and score metadata for the selected snapshot game.\n\nSELECT\n v.*,\n CASE WHEN UPPER(v.HomeTeamName) = 'CLEMSON' THEN 'Clemson' ELSE 'Opponent' END AS HomeSide\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data v\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(v.GameID AS VARCHAR) = sg.GameID","inputQueryString":"-- Fetches venue and score metadata for the selected snapshot game.\n\nSELECT\n v.*,\n CASE WHEN UPPER(v.HomeTeamName) = 'CLEMSON' THEN 'Clemson' ELSE 'Opponent' END AS HomeSide\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data v\nINNER JOIN ${selected_game} sg\n ON CAST(v.GameID AS VARCHAR) = sg.GameID","compiled":true,"inline":false},{"id":"selected_plays","compiledQueryString":"-- Extracts play-by-play rows for the selected game with team side and run-on-play calculations.\n\nSELECT\n p.TopBottom,\n p.InningNumber,\n p.PlayDescription,\n LOWER(COALESCE(p.PlayDescription, '')) AS PlayText,\n CASE\n WHEN UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID THEN 'Clemson'\n WHEN UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID THEN 'Clemson'\n WHEN UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID THEN 'Opponent'\n WHEN UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID THEN 'Opponent'\n END AS BattingSide,\n CASE WHEN LOWER(COALESCE(p.PlayDescription, '')) LIKE '%stole home%' OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%homered%' THEN 1 ELSE 0 END\n +\n CASE\n WHEN LOWER(COALESCE(p.PlayDescription, '')) LIKE '%scored%'\n THEN (LENGTH(LOWER(p.PlayDescription)) - LENGTH(REPLACE(LOWER(p.PlayDescription), 'scored', ''))) / 6\n ELSE 0\n END AS RunsOnPlay\nFROM memory.SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play p\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(p.GameID AS VARCHAR) = sg.GameID","inputQueryString":"-- Extracts play-by-play rows for the selected game with team side and run-on-play calculations.\n\nSELECT\n p.TopBottom,\n p.InningNumber,\n p.PlayDescription,\n LOWER(COALESCE(p.PlayDescription, '')) AS PlayText,\n CASE\n WHEN UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID THEN 'Clemson'\n WHEN UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID THEN 'Clemson'\n WHEN UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID THEN 'Opponent'\n WHEN UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID THEN 'Opponent'\n END AS BattingSide,\n CASE WHEN LOWER(COALESCE(p.PlayDescription, '')) LIKE '%stole home%' OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%homered%' THEN 1 ELSE 0 END\n +\n CASE\n WHEN LOWER(COALESCE(p.PlayDescription, '')) LIKE '%scored%'\n THEN (LENGTH(LOWER(p.PlayDescription)) - LENGTH(REPLACE(LOWER(p.PlayDescription), 'scored', ''))) / 6\n ELSE 0\n END AS RunsOnPlay\nFROM memory.SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play p\nINNER JOIN ${selected_game} sg\n ON CAST(p.GameID AS VARCHAR) = sg.GameID","compiled":true,"inline":false},{"id":"game_snapshot","compiledQueryString":"-- Returns high-level score, date, season, and result fields for the selected game.\n\nSELECT\n GameDate,\n Season,\n SeasonType,\n HomeTeamName,\n OpponentName,\n Clemson_Final_Runs,\n Opponent_Final_Runs,\n CASE WHEN Clemson_Final_Runs > Opponent_Final_Runs THEN 'W' ELSE 'L' END AS Result\nFROM (-- Fetches venue and score metadata for the selected snapshot game.\n\nSELECT\n v.*,\n CASE WHEN UPPER(v.HomeTeamName) = 'CLEMSON' THEN 'Clemson' ELSE 'Opponent' END AS HomeSide\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data v\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(v.GameID AS VARCHAR) = sg.GameID)","inputQueryString":"-- Returns high-level score, date, season, and result fields for the selected game.\n\nSELECT\n GameDate,\n Season,\n SeasonType,\n HomeTeamName,\n OpponentName,\n Clemson_Final_Runs,\n Opponent_Final_Runs,\n CASE WHEN Clemson_Final_Runs > Opponent_Final_Runs THEN 'W' ELSE 'L' END AS Result\nFROM ${selected_game_meta}","compiled":true,"inline":false},{"id":"line_score_innings","compiledQueryString":"-- Lists innings present in the selected game for line score columns.\n\nSELECT DISTINCT InningNumber\nFROM (-- Extracts play-by-play rows for the selected game with team side and run-on-play calculations.\n\nSELECT\n p.TopBottom,\n p.InningNumber,\n p.PlayDescription,\n LOWER(COALESCE(p.PlayDescription, '')) AS PlayText,\n CASE\n WHEN UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID THEN 'Clemson'\n WHEN UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID THEN 'Clemson'\n WHEN UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID THEN 'Opponent'\n WHEN UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID THEN 'Opponent'\n END AS BattingSide,\n CASE WHEN LOWER(COALESCE(p.PlayDescription, '')) LIKE '%stole home%' OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%homered%' THEN 1 ELSE 0 END\n +\n CASE\n WHEN LOWER(COALESCE(p.PlayDescription, '')) LIKE '%scored%'\n THEN (LENGTH(LOWER(p.PlayDescription)) - LENGTH(REPLACE(LOWER(p.PlayDescription), 'scored', ''))) / 6\n ELSE 0\n END AS RunsOnPlay\nFROM memory.SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play p\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(p.GameID AS VARCHAR) = sg.GameID)\nWHERE InningNumber IS NOT NULL\nORDER BY InningNumber","inputQueryString":"-- Lists innings present in the selected game for line score columns.\n\nSELECT DISTINCT InningNumber\nFROM ${selected_plays}\nWHERE InningNumber IS NOT NULL\nORDER BY InningNumber","compiled":true,"inline":false},{"id":"game_line_score","compiledQueryString":"-- Builds the line score table by team side and inning for the selected game.\n\nWITH sides AS (\n SELECT\n 'Clemson' AS TeamSide,\n 'Clemson' AS TeamName,\n 1 AS SortOrder,\n Clemson_Final_Runs AS FinalRuns\n FROM (-- Fetches venue and score metadata for the selected snapshot game.\n\nSELECT\n v.*,\n CASE WHEN UPPER(v.HomeTeamName) = 'CLEMSON' THEN 'Clemson' ELSE 'Opponent' END AS HomeSide\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data v\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(v.GameID AS VARCHAR) = sg.GameID)\n\n UNION ALL\n\n SELECT\n 'Opponent' AS TeamSide,\n OpponentName AS TeamName,\n 2 AS SortOrder,\n Opponent_Final_Runs AS FinalRuns\n FROM (-- Fetches venue and score metadata for the selected snapshot game.\n\nSELECT\n v.*,\n CASE WHEN UPPER(v.HomeTeamName) = 'CLEMSON' THEN 'Clemson' ELSE 'Opponent' END AS HomeSide\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data v\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(v.GameID AS VARCHAR) = sg.GameID)\n),\nruns_by_inning AS (\n SELECT\n BattingSide AS TeamSide,\n InningNumber,\n SUM(RunsOnPlay) AS InningScore\n FROM (-- Extracts play-by-play rows for the selected game with team side and run-on-play calculations.\n\nSELECT\n p.TopBottom,\n p.InningNumber,\n p.PlayDescription,\n LOWER(COALESCE(p.PlayDescription, '')) AS PlayText,\n CASE\n WHEN UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID THEN 'Clemson'\n WHEN UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID THEN 'Clemson'\n WHEN UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID THEN 'Opponent'\n WHEN UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID THEN 'Opponent'\n END AS BattingSide,\n CASE WHEN LOWER(COALESCE(p.PlayDescription, '')) LIKE '%stole home%' OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%homered%' THEN 1 ELSE 0 END\n +\n CASE\n WHEN LOWER(COALESCE(p.PlayDescription, '')) LIKE '%scored%'\n THEN (LENGTH(LOWER(p.PlayDescription)) - LENGTH(REPLACE(LOWER(p.PlayDescription), 'scored', ''))) / 6\n ELSE 0\n END AS RunsOnPlay\nFROM memory.SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play p\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(p.GameID AS VARCHAR) = sg.GameID)\n WHERE BattingSide IS NOT NULL\n GROUP BY BattingSide, InningNumber\n),\ninning_bounds AS (\n SELECT\n MAX(CASE WHEN UPPER(TRIM(TopBottom)) = 'TOP' THEN InningNumber END) AS TopMaxInning,\n MAX(CASE WHEN UPPER(TRIM(TopBottom)) = 'BOTTOM' THEN InningNumber END) AS BottomMaxInning\n FROM (-- Extracts play-by-play rows for the selected game with team side and run-on-play calculations.\n\nSELECT\n p.TopBottom,\n p.InningNumber,\n p.PlayDescription,\n LOWER(COALESCE(p.PlayDescription, '')) AS PlayText,\n CASE\n WHEN UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID THEN 'Clemson'\n WHEN UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID THEN 'Clemson'\n WHEN UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID THEN 'Opponent'\n WHEN UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID THEN 'Opponent'\n END AS BattingSide,\n CASE WHEN LOWER(COALESCE(p.PlayDescription, '')) LIKE '%stole home%' OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%homered%' THEN 1 ELSE 0 END\n +\n CASE\n WHEN LOWER(COALESCE(p.PlayDescription, '')) LIKE '%scored%'\n THEN (LENGTH(LOWER(p.PlayDescription)) - LENGTH(REPLACE(LOWER(p.PlayDescription), 'scored', ''))) / 6\n ELSE 0\n END AS RunsOnPlay\nFROM memory.SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play p\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(p.GameID AS VARCHAR) = sg.GameID)\n)\nSELECT\n s.TeamSide,\n s.TeamName,\n s.SortOrder,\n s.FinalRuns,\n i.InningNumber,\n CAST(COALESCE(r.InningScore, 0) AS INTEGER) AS InningScore,\n CASE\n WHEN s.TeamSide = m.HomeSide\n AND i.InningNumber = b.TopMaxInning\n AND b.TopMaxInning > COALESCE(b.BottomMaxInning, 0)\n THEN '-'\n ELSE CAST(CAST(COALESCE(r.InningScore, 0) AS INTEGER) AS VARCHAR)\n END AS DisplayScore\nFROM sides s\nCROSS JOIN (-- Lists innings present in the selected game for line score columns.\n\nSELECT DISTINCT InningNumber\nFROM (-- Extracts play-by-play rows for the selected game with team side and run-on-play calculations.\n\nSELECT\n p.TopBottom,\n p.InningNumber,\n p.PlayDescription,\n LOWER(COALESCE(p.PlayDescription, '')) AS PlayText,\n CASE\n WHEN UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID THEN 'Clemson'\n WHEN UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID THEN 'Clemson'\n WHEN UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID THEN 'Opponent'\n WHEN UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID THEN 'Opponent'\n END AS BattingSide,\n CASE WHEN LOWER(COALESCE(p.PlayDescription, '')) LIKE '%stole home%' OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%homered%' THEN 1 ELSE 0 END\n +\n CASE\n WHEN LOWER(COALESCE(p.PlayDescription, '')) LIKE '%scored%'\n THEN (LENGTH(LOWER(p.PlayDescription)) - LENGTH(REPLACE(LOWER(p.PlayDescription), 'scored', ''))) / 6\n ELSE 0\n END AS RunsOnPlay\nFROM memory.SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play p\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(p.GameID AS VARCHAR) = sg.GameID)\nWHERE InningNumber IS NOT NULL\nORDER BY InningNumber) i\nCROSS JOIN (-- Fetches venue and score metadata for the selected snapshot game.\n\nSELECT\n v.*,\n CASE WHEN UPPER(v.HomeTeamName) = 'CLEMSON' THEN 'Clemson' ELSE 'Opponent' END AS HomeSide\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data v\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(v.GameID AS VARCHAR) = sg.GameID) m\nCROSS JOIN inning_bounds b\nLEFT JOIN runs_by_inning r\n ON r.TeamSide = s.TeamSide\n AND r.InningNumber = i.InningNumber\nORDER BY s.SortOrder, i.InningNumber","inputQueryString":"-- Builds the line score table by team side and inning for the selected game.\n\nWITH sides AS (\n SELECT\n 'Clemson' AS TeamSide,\n 'Clemson' AS TeamName,\n 1 AS SortOrder,\n Clemson_Final_Runs AS FinalRuns\n FROM ${selected_game_meta}\n\n UNION ALL\n\n SELECT\n 'Opponent' AS TeamSide,\n OpponentName AS TeamName,\n 2 AS SortOrder,\n Opponent_Final_Runs AS FinalRuns\n FROM ${selected_game_meta}\n),\nruns_by_inning AS (\n SELECT\n BattingSide AS TeamSide,\n InningNumber,\n SUM(RunsOnPlay) AS InningScore\n FROM ${selected_plays}\n WHERE BattingSide IS NOT NULL\n GROUP BY BattingSide, InningNumber\n),\ninning_bounds AS (\n SELECT\n MAX(CASE WHEN UPPER(TRIM(TopBottom)) = 'TOP' THEN InningNumber END) AS TopMaxInning,\n MAX(CASE WHEN UPPER(TRIM(TopBottom)) = 'BOTTOM' THEN InningNumber END) AS BottomMaxInning\n FROM ${selected_plays}\n)\nSELECT\n s.TeamSide,\n s.TeamName,\n s.SortOrder,\n s.FinalRuns,\n i.InningNumber,\n CAST(COALESCE(r.InningScore, 0) AS INTEGER) AS InningScore,\n CASE\n WHEN s.TeamSide = m.HomeSide\n AND i.InningNumber = b.TopMaxInning\n AND b.TopMaxInning > COALESCE(b.BottomMaxInning, 0)\n THEN '-'\n ELSE CAST(CAST(COALESCE(r.InningScore, 0) AS INTEGER) AS VARCHAR)\n END AS DisplayScore\nFROM sides s\nCROSS JOIN ${line_score_innings} i\nCROSS JOIN ${selected_game_meta} m\nCROSS JOIN inning_bounds b\nLEFT JOIN runs_by_inning r\n ON r.TeamSide = s.TeamSide\n AND r.InningNumber = i.InningNumber\nORDER BY s.SortOrder, i.InningNumber","compiled":true,"inline":false},{"id":"team_summary","compiledQueryString":"-- Combines score and play-by-play-derived totals for each side in the selected game.\n\nWITH sides AS (\n SELECT\n 'Clemson' AS TeamSide,\n 'Clemson' AS TeamName,\n 1 AS SortOrder,\n Clemson_Final_Runs AS FinalRuns\n FROM (-- Fetches venue and score metadata for the selected snapshot game.\n\nSELECT\n v.*,\n CASE WHEN UPPER(v.HomeTeamName) = 'CLEMSON' THEN 'Clemson' ELSE 'Opponent' END AS HomeSide\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data v\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(v.GameID AS VARCHAR) = sg.GameID)\n\n UNION ALL\n\n SELECT\n 'Opponent' AS TeamSide,\n OpponentName AS TeamName,\n 2 AS SortOrder,\n Opponent_Final_Runs AS FinalRuns\n FROM (-- Fetches venue and score metadata for the selected snapshot game.\n\nSELECT\n v.*,\n CASE WHEN UPPER(v.HomeTeamName) = 'CLEMSON' THEN 'Clemson' ELSE 'Opponent' END AS HomeSide\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data v\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(v.GameID AS VARCHAR) = sg.GameID)\n),\nplay_totals AS (\n SELECT\n BattingSide AS TeamSide,\n SUM(CASE WHEN PlayText LIKE '%struck out%' THEN 1 ELSE 0 END) AS StrikeOuts,\n SUM(CASE WHEN PlayText LIKE '%stole%' THEN 1 ELSE 0 END) AS StolenBases,\n SUM(CASE WHEN PlayText LIKE '%caught stealing%' THEN 1 ELSE 0 END) AS CaughtStealing,\n SUM(CASE WHEN PlayText LIKE '%singled%' THEN 1 ELSE 0 END) AS Singles,\n SUM(CASE WHEN PlayText LIKE '%doubled%' THEN 1 ELSE 0 END) AS Doubles,\n SUM(CASE WHEN PlayText LIKE '%tripled%' THEN 1 ELSE 0 END) AS Triples,\n SUM(CASE WHEN PlayDescription LIKE '%RBI%' THEN 1 ELSE 0 END) AS RBI,\n SUM(CASE WHEN PlayText LIKE '%homered%' THEN 1 ELSE 0 END) AS HomeRuns\n FROM (-- Extracts play-by-play rows for the selected game with team side and run-on-play calculations.\n\nSELECT\n p.TopBottom,\n p.InningNumber,\n p.PlayDescription,\n LOWER(COALESCE(p.PlayDescription, '')) AS PlayText,\n CASE\n WHEN UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID THEN 'Clemson'\n WHEN UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID THEN 'Clemson'\n WHEN UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID THEN 'Opponent'\n WHEN UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID THEN 'Opponent'\n END AS BattingSide,\n CASE WHEN LOWER(COALESCE(p.PlayDescription, '')) LIKE '%stole home%' OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%homered%' THEN 1 ELSE 0 END\n +\n CASE\n WHEN LOWER(COALESCE(p.PlayDescription, '')) LIKE '%scored%'\n THEN (LENGTH(LOWER(p.PlayDescription)) - LENGTH(REPLACE(LOWER(p.PlayDescription), 'scored', ''))) / 6\n ELSE 0\n END AS RunsOnPlay\nFROM memory.SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play p\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(p.GameID AS VARCHAR) = sg.GameID)\n WHERE BattingSide IS NOT NULL\n GROUP BY BattingSide\n)\nSELECT\n s.TeamSide,\n s.TeamName,\n s.SortOrder,\n s.FinalRuns,\n COALESCE(t.StrikeOuts, 0) AS StrikeOuts,\n COALESCE(t.StolenBases, 0) AS StolenBases,\n COALESCE(t.CaughtStealing, 0) AS CaughtStealing,\n COALESCE(t.Singles, 0) AS Singles,\n COALESCE(t.Doubles, 0) AS Doubles,\n COALESCE(t.Triples, 0) AS Triples,\n COALESCE(t.RBI, 0) AS RBI,\n COALESCE(t.HomeRuns, 0) AS HomeRuns,\n s.FinalRuns AS Runs\nFROM sides s\nLEFT JOIN play_totals t\n ON t.TeamSide = s.TeamSide\nORDER BY s.SortOrder","inputQueryString":"-- Combines score and play-by-play-derived totals for each side in the selected game.\n\nWITH sides AS (\n SELECT\n 'Clemson' AS TeamSide,\n 'Clemson' AS TeamName,\n 1 AS SortOrder,\n Clemson_Final_Runs AS FinalRuns\n FROM ${selected_game_meta}\n\n UNION ALL\n\n SELECT\n 'Opponent' AS TeamSide,\n OpponentName AS TeamName,\n 2 AS SortOrder,\n Opponent_Final_Runs AS FinalRuns\n FROM ${selected_game_meta}\n),\nplay_totals AS (\n SELECT\n BattingSide AS TeamSide,\n SUM(CASE WHEN PlayText LIKE '%struck out%' THEN 1 ELSE 0 END) AS StrikeOuts,\n SUM(CASE WHEN PlayText LIKE '%stole%' THEN 1 ELSE 0 END) AS StolenBases,\n SUM(CASE WHEN PlayText LIKE '%caught stealing%' THEN 1 ELSE 0 END) AS CaughtStealing,\n SUM(CASE WHEN PlayText LIKE '%singled%' THEN 1 ELSE 0 END) AS Singles,\n SUM(CASE WHEN PlayText LIKE '%doubled%' THEN 1 ELSE 0 END) AS Doubles,\n SUM(CASE WHEN PlayText LIKE '%tripled%' THEN 1 ELSE 0 END) AS Triples,\n SUM(CASE WHEN PlayDescription LIKE '%RBI%' THEN 1 ELSE 0 END) AS RBI,\n SUM(CASE WHEN PlayText LIKE '%homered%' THEN 1 ELSE 0 END) AS HomeRuns\n FROM ${selected_plays}\n WHERE BattingSide IS NOT NULL\n GROUP BY BattingSide\n)\nSELECT\n s.TeamSide,\n s.TeamName,\n s.SortOrder,\n s.FinalRuns,\n COALESCE(t.StrikeOuts, 0) AS StrikeOuts,\n COALESCE(t.StolenBases, 0) AS StolenBases,\n COALESCE(t.CaughtStealing, 0) AS CaughtStealing,\n COALESCE(t.Singles, 0) AS Singles,\n COALESCE(t.Doubles, 0) AS Doubles,\n COALESCE(t.Triples, 0) AS Triples,\n COALESCE(t.RBI, 0) AS RBI,\n COALESCE(t.HomeRuns, 0) AS HomeRuns,\n s.FinalRuns AS Runs\nFROM sides s\nLEFT JOIN play_totals t\n ON t.TeamSide = s.TeamSide\nORDER BY s.SortOrder","compiled":true,"inline":false},{"id":"game_snapshot_players","compiledQueryString":"-- Aggregates player batting, fielding, and pitching lines for both sides in the selected game.\n\nSELECT\n CASE WHEN g.IsClemson = 1 THEN 'Clemson' ELSE 'Opponent' END AS TeamSide,\n CASE WHEN g.IsClemson = 1 THEN 'Clemson' ELSE g.TeamName END AS TeamName,\n g.PlayerName,\n g.PlayerNumber,\n CASE\n WHEN ('/' || LOWER(REPLACE(COALESCE(g.Position, ''), ' ', '')) || '/') LIKE '%/p/%' THEN 'Pitcher'\n WHEN LOWER(TRIM(g.Position)) IN ('1b') THEN 'First Base'\n WHEN LOWER(TRIM(g.Position)) IN ('2b') THEN 'Second Base'\n WHEN LOWER(TRIM(g.Position)) IN ('3b') THEN 'Third Base'\n WHEN LOWER(TRIM(g.Position)) IN ('ss') THEN 'Short Stop'\n WHEN LOWER(TRIM(g.Position)) IN ('c') THEN 'Catcher'\n WHEN LOWER(TRIM(g.Position)) IN ('lf') THEN 'Left Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('rf') THEN 'Right Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('cf') THEN 'Center Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('pr') THEN 'Pinch Runner'\n WHEN LOWER(TRIM(g.Position)) IN ('dp') THEN 'Designated Player'\n ELSE COALESCE(g.Position, '-')\n END AS Position,\n CASE\n WHEN ('/' || LOWER(REPLACE(COALESCE(g.Position, ''), ' ', '')) || '/') LIKE '%/p/%' THEN 1\n ELSE 0\n END AS IsPitcher,\n COALESCE(SUM(Hitter_AtBats)) AS AtBats,\n COALESCE(SUM(Hitter_Runs)) AS Runs,\n COALESCE(SUM(Hitter_Hits)) AS Hits,\n COALESCE(SUM(Hitter_RunnersBattedIn)) AS RBIs,\n COALESCE(SUM(Hitter_BaseOnBalls)) AS Walks,\n COALESCE(SUM(Hitter_StrikeOuts)) AS SO,\n COALESCE(SUM(Field_PutOuts)) AS PO,\n COALESCE(SUM(Field_Assists)) AS A,\n COALESCE(SUM(HSit_LeftOnBase)) AS LOB,\n FLOOR(SUM(CAST(FLOOR(COALESCE(Pitcher_InningsPitched, 0)) AS INTEGER) * 3\n + CAST(ROUND((COALESCE(Pitcher_InningsPitched, 0) - FLOOR(COALESCE(Pitcher_InningsPitched, 0))) * 10) AS INTEGER)) / 3.0) + (MOD(SUM(CAST(FLOOR(COALESCE(Pitcher_InningsPitched, 0)) AS INTEGER) * 3\n + CAST(ROUND((COALESCE(Pitcher_InningsPitched, 0) - FLOOR(COALESCE(Pitcher_InningsPitched, 0))) * 10) AS INTEGER)), 3) / 10.0) AS IP,\n COALESCE(SUM(Pitcher_Hits)) AS PHits,\n COALESCE(SUM(Pitcher_Runs)) AS PRuns,\n COALESCE(SUM(Pitcher_EarnedRuns)) AS PER,\n COALESCE(SUM(Pitcher_BaseOnBalls)) AS PWalks,\n COALESCE(SUM(Pitcher_StrikeOuts)) AS PSO,\n COALESCE(SUM(Pitcher_AtBats)) AS PAtBats,\n COALESCE(SUM(Pitcher_BattersFaced)) AS PBF,\n COALESCE(SUM(Pitcher_Pitches)) AS NP,\n CASE WHEN g.IsClemson = 1 THEN '/player-profiles/' || g.PlayerName END AS link\nFROM memory.SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS g\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(g.GameID AS VARCHAR) = sg.GameID\nGROUP BY\n g.GameID,\n g.GameDate,\n g.IsClemson,\n g.TeamName,\n g.PlayerName,\n g.PlayerNumber,\n g.Position\nHAVING COALESCE(MAX(g.GamesPlayed), 0) >= 1\nORDER BY TeamSide, g.PlayerName","inputQueryString":"-- Aggregates player batting, fielding, and pitching lines for both sides in the selected game.\n\nSELECT\n CASE WHEN g.IsClemson = 1 THEN 'Clemson' ELSE 'Opponent' END AS TeamSide,\n CASE WHEN g.IsClemson = 1 THEN 'Clemson' ELSE g.TeamName END AS TeamName,\n g.PlayerName,\n g.PlayerNumber,\n CASE\n WHEN ('/' || LOWER(REPLACE(COALESCE(g.Position, ''), ' ', '')) || '/') LIKE '%/p/%' THEN 'Pitcher'\n WHEN LOWER(TRIM(g.Position)) IN ('1b') THEN 'First Base'\n WHEN LOWER(TRIM(g.Position)) IN ('2b') THEN 'Second Base'\n WHEN LOWER(TRIM(g.Position)) IN ('3b') THEN 'Third Base'\n WHEN LOWER(TRIM(g.Position)) IN ('ss') THEN 'Short Stop'\n WHEN LOWER(TRIM(g.Position)) IN ('c') THEN 'Catcher'\n WHEN LOWER(TRIM(g.Position)) IN ('lf') THEN 'Left Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('rf') THEN 'Right Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('cf') THEN 'Center Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('pr') THEN 'Pinch Runner'\n WHEN LOWER(TRIM(g.Position)) IN ('dp') THEN 'Designated Player'\n ELSE COALESCE(g.Position, '-')\n END AS Position,\n CASE\n WHEN ('/' || LOWER(REPLACE(COALESCE(g.Position, ''), ' ', '')) || '/') LIKE '%/p/%' THEN 1\n ELSE 0\n END AS IsPitcher,\n COALESCE(SUM(Hitter_AtBats)) AS AtBats,\n COALESCE(SUM(Hitter_Runs)) AS Runs,\n COALESCE(SUM(Hitter_Hits)) AS Hits,\n COALESCE(SUM(Hitter_RunnersBattedIn)) AS RBIs,\n COALESCE(SUM(Hitter_BaseOnBalls)) AS Walks,\n COALESCE(SUM(Hitter_StrikeOuts)) AS SO,\n COALESCE(SUM(Field_PutOuts)) AS PO,\n COALESCE(SUM(Field_Assists)) AS A,\n COALESCE(SUM(HSit_LeftOnBase)) AS LOB,\n FLOOR(SUM(CAST(FLOOR(COALESCE(Pitcher_InningsPitched, 0)) AS INTEGER) * 3\n + CAST(ROUND((COALESCE(Pitcher_InningsPitched, 0) - FLOOR(COALESCE(Pitcher_InningsPitched, 0))) * 10) AS INTEGER)) / 3.0) + (MOD(SUM(CAST(FLOOR(COALESCE(Pitcher_InningsPitched, 0)) AS INTEGER) * 3\n + CAST(ROUND((COALESCE(Pitcher_InningsPitched, 0) - FLOOR(COALESCE(Pitcher_InningsPitched, 0))) * 10) AS INTEGER)), 3) / 10.0) AS IP,\n COALESCE(SUM(Pitcher_Hits)) AS PHits,\n COALESCE(SUM(Pitcher_Runs)) AS PRuns,\n COALESCE(SUM(Pitcher_EarnedRuns)) AS PER,\n COALESCE(SUM(Pitcher_BaseOnBalls)) AS PWalks,\n COALESCE(SUM(Pitcher_StrikeOuts)) AS PSO,\n COALESCE(SUM(Pitcher_AtBats)) AS PAtBats,\n COALESCE(SUM(Pitcher_BattersFaced)) AS PBF,\n COALESCE(SUM(Pitcher_Pitches)) AS NP,\n CASE WHEN g.IsClemson = 1 THEN '/player-profiles/' || g.PlayerName END AS link\nFROM memory.SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS g\nINNER JOIN ${selected_game} sg\n ON CAST(g.GameID AS VARCHAR) = sg.GameID\nGROUP BY\n g.GameID,\n g.GameDate,\n g.IsClemson,\n g.TeamName,\n g.PlayerName,\n g.PlayerNumber,\n g.Position\nHAVING COALESCE(MAX(g.GamesPlayed), 0) >= 1\nORDER BY TeamSide, g.PlayerName","compiled":true,"inline":false},{"id":"game_snapshot_player","compiledQueryString":"-- Filters snapshot players down to all Clemson players.\n\nSELECT *\nFROM (-- Aggregates player batting, fielding, and pitching lines for both sides in the selected game.\n\nSELECT\n CASE WHEN g.IsClemson = 1 THEN 'Clemson' ELSE 'Opponent' END AS TeamSide,\n CASE WHEN g.IsClemson = 1 THEN 'Clemson' ELSE g.TeamName END AS TeamName,\n g.PlayerName,\n g.PlayerNumber,\n CASE\n WHEN ('/' || LOWER(REPLACE(COALESCE(g.Position, ''), ' ', '')) || '/') LIKE '%/p/%' THEN 'Pitcher'\n WHEN LOWER(TRIM(g.Position)) IN ('1b') THEN 'First Base'\n WHEN LOWER(TRIM(g.Position)) IN ('2b') THEN 'Second Base'\n WHEN LOWER(TRIM(g.Position)) IN ('3b') THEN 'Third Base'\n WHEN LOWER(TRIM(g.Position)) IN ('ss') THEN 'Short Stop'\n WHEN LOWER(TRIM(g.Position)) IN ('c') THEN 'Catcher'\n WHEN LOWER(TRIM(g.Position)) IN ('lf') THEN 'Left Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('rf') THEN 'Right Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('cf') THEN 'Center Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('pr') THEN 'Pinch Runner'\n WHEN LOWER(TRIM(g.Position)) IN ('dp') THEN 'Designated Player'\n ELSE COALESCE(g.Position, '-')\n END AS Position,\n CASE\n WHEN ('/' || LOWER(REPLACE(COALESCE(g.Position, ''), ' ', '')) || '/') LIKE '%/p/%' THEN 1\n ELSE 0\n END AS IsPitcher,\n COALESCE(SUM(Hitter_AtBats)) AS AtBats,\n COALESCE(SUM(Hitter_Runs)) AS Runs,\n COALESCE(SUM(Hitter_Hits)) AS Hits,\n COALESCE(SUM(Hitter_RunnersBattedIn)) AS RBIs,\n COALESCE(SUM(Hitter_BaseOnBalls)) AS Walks,\n COALESCE(SUM(Hitter_StrikeOuts)) AS SO,\n COALESCE(SUM(Field_PutOuts)) AS PO,\n COALESCE(SUM(Field_Assists)) AS A,\n COALESCE(SUM(HSit_LeftOnBase)) AS LOB,\n FLOOR(SUM(CAST(FLOOR(COALESCE(Pitcher_InningsPitched, 0)) AS INTEGER) * 3\n + CAST(ROUND((COALESCE(Pitcher_InningsPitched, 0) - FLOOR(COALESCE(Pitcher_InningsPitched, 0))) * 10) AS INTEGER)) / 3.0) + (MOD(SUM(CAST(FLOOR(COALESCE(Pitcher_InningsPitched, 0)) AS INTEGER) * 3\n + CAST(ROUND((COALESCE(Pitcher_InningsPitched, 0) - FLOOR(COALESCE(Pitcher_InningsPitched, 0))) * 10) AS INTEGER)), 3) / 10.0) AS IP,\n COALESCE(SUM(Pitcher_Hits)) AS PHits,\n COALESCE(SUM(Pitcher_Runs)) AS PRuns,\n COALESCE(SUM(Pitcher_EarnedRuns)) AS PER,\n COALESCE(SUM(Pitcher_BaseOnBalls)) AS PWalks,\n COALESCE(SUM(Pitcher_StrikeOuts)) AS PSO,\n COALESCE(SUM(Pitcher_AtBats)) AS PAtBats,\n COALESCE(SUM(Pitcher_BattersFaced)) AS PBF,\n COALESCE(SUM(Pitcher_Pitches)) AS NP,\n CASE WHEN g.IsClemson = 1 THEN '/player-profiles/' || g.PlayerName END AS link\nFROM memory.SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS g\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(g.GameID AS VARCHAR) = sg.GameID\nGROUP BY\n g.GameID,\n g.GameDate,\n g.IsClemson,\n g.TeamName,\n g.PlayerName,\n g.PlayerNumber,\n g.Position\nHAVING COALESCE(MAX(g.GamesPlayed), 0) >= 1\nORDER BY TeamSide, g.PlayerName)\nWHERE TeamSide = 'Clemson'\nORDER BY PlayerName","inputQueryString":"-- Filters snapshot players down to all Clemson players.\n\nSELECT *\nFROM ${game_snapshot_players}\nWHERE TeamSide = 'Clemson'\nORDER BY PlayerName","compiled":true,"inline":false},{"id":"game_snapshot_pitchers","compiledQueryString":"-- Filters snapshot players down to Clemson pitchers.\n\nSELECT *\nFROM (-- Aggregates player batting, fielding, and pitching lines for both sides in the selected game.\n\nSELECT\n CASE WHEN g.IsClemson = 1 THEN 'Clemson' ELSE 'Opponent' END AS TeamSide,\n CASE WHEN g.IsClemson = 1 THEN 'Clemson' ELSE g.TeamName END AS TeamName,\n g.PlayerName,\n g.PlayerNumber,\n CASE\n WHEN ('/' || LOWER(REPLACE(COALESCE(g.Position, ''), ' ', '')) || '/') LIKE '%/p/%' THEN 'Pitcher'\n WHEN LOWER(TRIM(g.Position)) IN ('1b') THEN 'First Base'\n WHEN LOWER(TRIM(g.Position)) IN ('2b') THEN 'Second Base'\n WHEN LOWER(TRIM(g.Position)) IN ('3b') THEN 'Third Base'\n WHEN LOWER(TRIM(g.Position)) IN ('ss') THEN 'Short Stop'\n WHEN LOWER(TRIM(g.Position)) IN ('c') THEN 'Catcher'\n WHEN LOWER(TRIM(g.Position)) IN ('lf') THEN 'Left Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('rf') THEN 'Right Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('cf') THEN 'Center Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('pr') THEN 'Pinch Runner'\n WHEN LOWER(TRIM(g.Position)) IN ('dp') THEN 'Designated Player'\n ELSE COALESCE(g.Position, '-')\n END AS Position,\n CASE\n WHEN ('/' || LOWER(REPLACE(COALESCE(g.Position, ''), ' ', '')) || '/') LIKE '%/p/%' THEN 1\n ELSE 0\n END AS IsPitcher,\n COALESCE(SUM(Hitter_AtBats)) AS AtBats,\n COALESCE(SUM(Hitter_Runs)) AS Runs,\n COALESCE(SUM(Hitter_Hits)) AS Hits,\n COALESCE(SUM(Hitter_RunnersBattedIn)) AS RBIs,\n COALESCE(SUM(Hitter_BaseOnBalls)) AS Walks,\n COALESCE(SUM(Hitter_StrikeOuts)) AS SO,\n COALESCE(SUM(Field_PutOuts)) AS PO,\n COALESCE(SUM(Field_Assists)) AS A,\n COALESCE(SUM(HSit_LeftOnBase)) AS LOB,\n FLOOR(SUM(CAST(FLOOR(COALESCE(Pitcher_InningsPitched, 0)) AS INTEGER) * 3\n + CAST(ROUND((COALESCE(Pitcher_InningsPitched, 0) - FLOOR(COALESCE(Pitcher_InningsPitched, 0))) * 10) AS INTEGER)) / 3.0) + (MOD(SUM(CAST(FLOOR(COALESCE(Pitcher_InningsPitched, 0)) AS INTEGER) * 3\n + CAST(ROUND((COALESCE(Pitcher_InningsPitched, 0) - FLOOR(COALESCE(Pitcher_InningsPitched, 0))) * 10) AS INTEGER)), 3) / 10.0) AS IP,\n COALESCE(SUM(Pitcher_Hits)) AS PHits,\n COALESCE(SUM(Pitcher_Runs)) AS PRuns,\n COALESCE(SUM(Pitcher_EarnedRuns)) AS PER,\n COALESCE(SUM(Pitcher_BaseOnBalls)) AS PWalks,\n COALESCE(SUM(Pitcher_StrikeOuts)) AS PSO,\n COALESCE(SUM(Pitcher_AtBats)) AS PAtBats,\n COALESCE(SUM(Pitcher_BattersFaced)) AS PBF,\n COALESCE(SUM(Pitcher_Pitches)) AS NP,\n CASE WHEN g.IsClemson = 1 THEN '/player-profiles/' || g.PlayerName END AS link\nFROM memory.SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS g\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(g.GameID AS VARCHAR) = sg.GameID\nGROUP BY\n g.GameID,\n g.GameDate,\n g.IsClemson,\n g.TeamName,\n g.PlayerName,\n g.PlayerNumber,\n g.Position\nHAVING COALESCE(MAX(g.GamesPlayed), 0) >= 1\nORDER BY TeamSide, g.PlayerName)\nWHERE TeamSide = 'Clemson'\n AND IsPitcher = 1\nORDER BY PlayerName","inputQueryString":"-- Filters snapshot players down to Clemson pitchers.\n\nSELECT *\nFROM ${game_snapshot_players}\nWHERE TeamSide = 'Clemson'\n AND IsPitcher = 1\nORDER BY PlayerName","compiled":true,"inline":false},{"id":"game_snapshot_opp_player","compiledQueryString":"-- Filters snapshot players down to all opponent players.\n\nSELECT *\nFROM (-- Aggregates player batting, fielding, and pitching lines for both sides in the selected game.\n\nSELECT\n CASE WHEN g.IsClemson = 1 THEN 'Clemson' ELSE 'Opponent' END AS TeamSide,\n CASE WHEN g.IsClemson = 1 THEN 'Clemson' ELSE g.TeamName END AS TeamName,\n g.PlayerName,\n g.PlayerNumber,\n CASE\n WHEN ('/' || LOWER(REPLACE(COALESCE(g.Position, ''), ' ', '')) || '/') LIKE '%/p/%' THEN 'Pitcher'\n WHEN LOWER(TRIM(g.Position)) IN ('1b') THEN 'First Base'\n WHEN LOWER(TRIM(g.Position)) IN ('2b') THEN 'Second Base'\n WHEN LOWER(TRIM(g.Position)) IN ('3b') THEN 'Third Base'\n WHEN LOWER(TRIM(g.Position)) IN ('ss') THEN 'Short Stop'\n WHEN LOWER(TRIM(g.Position)) IN ('c') THEN 'Catcher'\n WHEN LOWER(TRIM(g.Position)) IN ('lf') THEN 'Left Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('rf') THEN 'Right Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('cf') THEN 'Center Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('pr') THEN 'Pinch Runner'\n WHEN LOWER(TRIM(g.Position)) IN ('dp') THEN 'Designated Player'\n ELSE COALESCE(g.Position, '-')\n END AS Position,\n CASE\n WHEN ('/' || LOWER(REPLACE(COALESCE(g.Position, ''), ' ', '')) || '/') LIKE '%/p/%' THEN 1\n ELSE 0\n END AS IsPitcher,\n COALESCE(SUM(Hitter_AtBats)) AS AtBats,\n COALESCE(SUM(Hitter_Runs)) AS Runs,\n COALESCE(SUM(Hitter_Hits)) AS Hits,\n COALESCE(SUM(Hitter_RunnersBattedIn)) AS RBIs,\n COALESCE(SUM(Hitter_BaseOnBalls)) AS Walks,\n COALESCE(SUM(Hitter_StrikeOuts)) AS SO,\n COALESCE(SUM(Field_PutOuts)) AS PO,\n COALESCE(SUM(Field_Assists)) AS A,\n COALESCE(SUM(HSit_LeftOnBase)) AS LOB,\n FLOOR(SUM(CAST(FLOOR(COALESCE(Pitcher_InningsPitched, 0)) AS INTEGER) * 3\n + CAST(ROUND((COALESCE(Pitcher_InningsPitched, 0) - FLOOR(COALESCE(Pitcher_InningsPitched, 0))) * 10) AS INTEGER)) / 3.0) + (MOD(SUM(CAST(FLOOR(COALESCE(Pitcher_InningsPitched, 0)) AS INTEGER) * 3\n + CAST(ROUND((COALESCE(Pitcher_InningsPitched, 0) - FLOOR(COALESCE(Pitcher_InningsPitched, 0))) * 10) AS INTEGER)), 3) / 10.0) AS IP,\n COALESCE(SUM(Pitcher_Hits)) AS PHits,\n COALESCE(SUM(Pitcher_Runs)) AS PRuns,\n COALESCE(SUM(Pitcher_EarnedRuns)) AS PER,\n COALESCE(SUM(Pitcher_BaseOnBalls)) AS PWalks,\n COALESCE(SUM(Pitcher_StrikeOuts)) AS PSO,\n COALESCE(SUM(Pitcher_AtBats)) AS PAtBats,\n COALESCE(SUM(Pitcher_BattersFaced)) AS PBF,\n COALESCE(SUM(Pitcher_Pitches)) AS NP,\n CASE WHEN g.IsClemson = 1 THEN '/player-profiles/' || g.PlayerName END AS link\nFROM memory.SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS g\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(g.GameID AS VARCHAR) = sg.GameID\nGROUP BY\n g.GameID,\n g.GameDate,\n g.IsClemson,\n g.TeamName,\n g.PlayerName,\n g.PlayerNumber,\n g.Position\nHAVING COALESCE(MAX(g.GamesPlayed), 0) >= 1\nORDER BY TeamSide, g.PlayerName)\nWHERE TeamSide = 'Opponent'\nORDER BY PlayerName","inputQueryString":"-- Filters snapshot players down to all opponent players.\n\nSELECT *\nFROM ${game_snapshot_players}\nWHERE TeamSide = 'Opponent'\nORDER BY PlayerName","compiled":true,"inline":false},{"id":"game_snapshot_opp_pitchers","compiledQueryString":"-- Filters snapshot players down to opponent pitchers.\n\nSELECT *\nFROM (-- Aggregates player batting, fielding, and pitching lines for both sides in the selected game.\n\nSELECT\n CASE WHEN g.IsClemson = 1 THEN 'Clemson' ELSE 'Opponent' END AS TeamSide,\n CASE WHEN g.IsClemson = 1 THEN 'Clemson' ELSE g.TeamName END AS TeamName,\n g.PlayerName,\n g.PlayerNumber,\n CASE\n WHEN ('/' || LOWER(REPLACE(COALESCE(g.Position, ''), ' ', '')) || '/') LIKE '%/p/%' THEN 'Pitcher'\n WHEN LOWER(TRIM(g.Position)) IN ('1b') THEN 'First Base'\n WHEN LOWER(TRIM(g.Position)) IN ('2b') THEN 'Second Base'\n WHEN LOWER(TRIM(g.Position)) IN ('3b') THEN 'Third Base'\n WHEN LOWER(TRIM(g.Position)) IN ('ss') THEN 'Short Stop'\n WHEN LOWER(TRIM(g.Position)) IN ('c') THEN 'Catcher'\n WHEN LOWER(TRIM(g.Position)) IN ('lf') THEN 'Left Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('rf') THEN 'Right Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('cf') THEN 'Center Fielder'\n WHEN LOWER(TRIM(g.Position)) IN ('pr') THEN 'Pinch Runner'\n WHEN LOWER(TRIM(g.Position)) IN ('dp') THEN 'Designated Player'\n ELSE COALESCE(g.Position, '-')\n END AS Position,\n CASE\n WHEN ('/' || LOWER(REPLACE(COALESCE(g.Position, ''), ' ', '')) || '/') LIKE '%/p/%' THEN 1\n ELSE 0\n END AS IsPitcher,\n COALESCE(SUM(Hitter_AtBats)) AS AtBats,\n COALESCE(SUM(Hitter_Runs)) AS Runs,\n COALESCE(SUM(Hitter_Hits)) AS Hits,\n COALESCE(SUM(Hitter_RunnersBattedIn)) AS RBIs,\n COALESCE(SUM(Hitter_BaseOnBalls)) AS Walks,\n COALESCE(SUM(Hitter_StrikeOuts)) AS SO,\n COALESCE(SUM(Field_PutOuts)) AS PO,\n COALESCE(SUM(Field_Assists)) AS A,\n COALESCE(SUM(HSit_LeftOnBase)) AS LOB,\n FLOOR(SUM(CAST(FLOOR(COALESCE(Pitcher_InningsPitched, 0)) AS INTEGER) * 3\n + CAST(ROUND((COALESCE(Pitcher_InningsPitched, 0) - FLOOR(COALESCE(Pitcher_InningsPitched, 0))) * 10) AS INTEGER)) / 3.0) + (MOD(SUM(CAST(FLOOR(COALESCE(Pitcher_InningsPitched, 0)) AS INTEGER) * 3\n + CAST(ROUND((COALESCE(Pitcher_InningsPitched, 0) - FLOOR(COALESCE(Pitcher_InningsPitched, 0))) * 10) AS INTEGER)), 3) / 10.0) AS IP,\n COALESCE(SUM(Pitcher_Hits)) AS PHits,\n COALESCE(SUM(Pitcher_Runs)) AS PRuns,\n COALESCE(SUM(Pitcher_EarnedRuns)) AS PER,\n COALESCE(SUM(Pitcher_BaseOnBalls)) AS PWalks,\n COALESCE(SUM(Pitcher_StrikeOuts)) AS PSO,\n COALESCE(SUM(Pitcher_AtBats)) AS PAtBats,\n COALESCE(SUM(Pitcher_BattersFaced)) AS PBF,\n COALESCE(SUM(Pitcher_Pitches)) AS NP,\n CASE WHEN g.IsClemson = 1 THEN '/player-profiles/' || g.PlayerName END AS link\nFROM memory.SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS g\nINNER JOIN (-- Resolves the requested snapshot game input, falling back to the latest filtered game.\n\nWITH requested_game AS (\n SELECT NULLIF(TRIM('${inputs.snapshot_game_choice?.value ?? ''}'), '') AS GameID\n)\nSELECT COALESCE(\n (\n SELECT g.value\n FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) g\n CROSS JOIN requested_game r\n WHERE r.GameID IS NOT NULL\n AND r.GameID <> '__latest__'\n AND g.value = r.GameID\n LIMIT 1\n ),\n (SELECT value FROM (-- Builds the selectable game list for the game snapshot page based on season and season-type filters.\n\nSELECT\n CAST(GameID AS VARCHAR) AS value,\n strftime(GameDate, '%Y-%m-%d') || ' · ' || Season || ' · ' || COALESCE(OpponentFullName, OpponentName) AS label,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nWHERE ('${inputs.snapshot_season.value}' = 'All Seasons' OR Season = '${inputs.snapshot_season.value}')\n AND (\n '${inputs.snapshot_season_type.value}' = 'All'\n OR SeasonType = '${inputs.snapshot_season_type.value}'\n OR ('${inputs.snapshot_season_type.value}' = 'POST' AND SeasonType IN ('ACC', 'NCAA'))\n )\nORDER BY GameDate DESC, value DESC) LIMIT 1),\n (SELECT GameID FROM (-- Finds the latest available Clemson game to use as the default game snapshot.\n\nSELECT\n CAST(GameID AS VARCHAR) AS GameID,\n CAST(Season AS VARCHAR) AS Season,\n SeasonType,\n GameDate\nFROM SPORTS_INFO_DW_REPORTING.venue_game_data\nORDER BY GameDate DESC, GameID DESC\nLIMIT 1) LIMIT 1)\n) AS GameID) sg\n ON CAST(g.GameID AS VARCHAR) = sg.GameID\nGROUP BY\n g.GameID,\n g.GameDate,\n g.IsClemson,\n g.TeamName,\n g.PlayerName,\n g.PlayerNumber,\n g.Position\nHAVING COALESCE(MAX(g.GamesPlayed), 0) >= 1\nORDER BY TeamSide, g.PlayerName)\nWHERE TeamSide = 'Opponent'\n AND IsPitcher = 1\nORDER BY PlayerName","inputQueryString":"-- Filters snapshot players down to opponent pitchers.\n\nSELECT *\nFROM ${game_snapshot_players}\nWHERE TeamSide = 'Opponent'\n AND IsPitcher = 1\nORDER BY PlayerName","compiled":true,"inline":false}]}

	1	2	3	4	5	6	7	Final
Clemson	0	0	0	0	0	0	0	0
Georgia	0	0	0	1	2	2	0	5

Game Snapshot

Select game

Final score

Team summary

Player Statistics