{"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":"individual_hitting_single_game_records","compiledQueryString":"-- Builds individual Clemson hitting single-game record tables.\n-- Split page-local SQL for pages/individual-records/hitting/index.md.\n-- Filter this query in the page with RecordKey to retrieve each record table.\n\nSELECT 'top_hitting_atbats_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY AtBats DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE AtBats > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, AtBats, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_atbats_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_hits_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY Hits DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE Hits > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, Hits, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_hits_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_doubles_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY Doubles DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE Doubles > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, Doubles, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_doubles_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_triples_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY Triples DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE Triples > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, Triples, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_triples_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_homeruns_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY HomeRun DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE HomeRun > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, HomeRun, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_homeruns_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_rbi_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY RunnersBattedIn DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE RunnersBattedIn > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, RunnersBattedIn, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_rbi_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_runs_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY Runs DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE Runs > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, Runs, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_runs_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_stolenbases_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY StolenBases DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE StolenBases > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, StolenBases, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_stolenbases_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_walks_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY BaseOnBalls DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE BaseOnBalls > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, BaseOnBalls, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_walks_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_hbp_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY HitByPitch DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE HitByPitch > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, HitByPitch, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_hbp_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_totalbases_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY TotalBases DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE TotalBases > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, TotalBases, Singles, Doubles, Triples, HomeRun, Hits, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_totalbases_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificeflies_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY SacrificeFlies DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE SacrificeFlies > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, SacrificeFlies, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_sacrificeflies_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificehits_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY SacrificeHits DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE SacrificeHits > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, SacrificeHits, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_sacrificehits_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificebunts_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY SacrificeBunts DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE SacrificeBunts > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, SacrificeBunts, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_sacrificebunts_single_game","inputQueryString":"-- Builds individual Clemson hitting single-game record tables.\n-- Split page-local SQL for pages/individual-records/hitting/index.md.\n-- Filter this query in the page with RecordKey to retrieve each record table.\n\nSELECT 'top_hitting_atbats_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY AtBats DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE AtBats > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, AtBats, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_atbats_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_hits_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY Hits DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE Hits > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, Hits, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_hits_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_doubles_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY Doubles DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE Doubles > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, Doubles, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_doubles_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_triples_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY Triples DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE Triples > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, Triples, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_triples_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_homeruns_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY HomeRun DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE HomeRun > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, HomeRun, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_homeruns_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_rbi_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY RunnersBattedIn DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE RunnersBattedIn > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, RunnersBattedIn, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_rbi_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_runs_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY Runs DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE Runs > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, Runs, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_runs_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_stolenbases_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY StolenBases DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE StolenBases > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, StolenBases, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_stolenbases_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_walks_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY BaseOnBalls DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE BaseOnBalls > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, BaseOnBalls, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_walks_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_hbp_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY HitByPitch DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE HitByPitch > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, HitByPitch, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_hbp_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_totalbases_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY TotalBases DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE TotalBases > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, TotalBases, Singles, Doubles, Triples, HomeRun, Hits, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_totalbases_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificeflies_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY SacrificeFlies DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE SacrificeFlies > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, SacrificeFlies, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_sacrificeflies_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificehits_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY SacrificeHits DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE SacrificeHits > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, SacrificeHits, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_sacrificehits_single_game\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificebunts_single_game' AS RecordKey, *\nFROM (\n WITH Ranked AS (\n SELECT RANK() OVER (ORDER BY SacrificeBunts DESC) AS Rank, *\n FROM (\n WITH game_context AS (\n SELECT\n GameID,\n MAX(GameDate) AS GameDate,\n MAX(Season) AS Season,\n MAX(Opponent) AS Opponent,\n CONCAT(MAX(WinLoss), ' ', CAST(MAX(TeamRuns) AS INT), '-', CAST(MAX(OpponentRuns) AS INT)) AS Score\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_team_stats_by_game\n WHERE IsClemson = 1\n GROUP BY GameID\n ),\n stats AS (\n SELECT\n p.PlayerName,\n p.GameID,\n MAX(COALESCE(g.GameDate, p.GameDate)) AS GameDate,\n MAX(COALESCE(g.Season, p.Season)) AS Season,\n MAX(g.Opponent) AS Opponent,\n MAX(g.Score) AS Score,\n CASE WHEN p.GameID IS NOT NULL THEN CONCAT('/game-snapshot/', CAST(p.GameID AS VARCHAR)) ELSE NULL END AS link,\n SUM(COALESCE(p.Hitter_AtBats, 0)) AS AtBats,\n SUM(COALESCE(p.Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(p.Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(p.Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(p.Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(p.Hitter_RunnersBattedIn, 0)) AS RunnersBattedIn,\n SUM(COALESCE(p.Hitter_Runs, 0)) AS Runs,\n SUM(COALESCE(p.Hitter_StolenBases, 0)) AS StolenBases,\n SUM(COALESCE(p.Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(p.Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(p.Hitter_Hits, 0) - COALESCE(p.Hitter_Doubles, 0) - COALESCE(p.Hitter_Triples, 0) - COALESCE(p.Hitter_HomeRun, 0)) AS Singles,\n SUM(\n COALESCE(p.Hitter_Hits, 0)\n - COALESCE(p.Hitter_Doubles, 0)\n - COALESCE(p.Hitter_Triples, 0)\n - COALESCE(p.Hitter_HomeRun, 0)\n + 2 * COALESCE(p.Hitter_Doubles, 0)\n + 3 * COALESCE(p.Hitter_Triples, 0)\n + 4 * COALESCE(p.Hitter_HomeRun, 0)\n ) AS TotalBases,\n SUM(COALESCE(p.Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(p.Hitter_SacrificeHits, 0)) AS SacrificeHits,\n COALESCE(MAX(sb.SacrificeBunts), 0) AS SacrificeBunts\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS p\n LEFT JOIN game_context AS g\n ON p.GameID = g.GameID\n LEFT JOIN (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n ) AS sb\n ON p.PlayerName = sb.PlayerName\n AND p.GameID = sb.GameID\n WHERE p.IsClemson = 1\n AND p.SeasonType IN ${inputs.single_game.value}\n GROUP BY p.PlayerName, p.GameID\n )\n SELECT *\n FROM stats\n )\n WHERE SacrificeBunts > 0\n )\n SELECT Rank, PlayerName, Season, GameDate, Opponent, Score, SacrificeBunts, GameID, link\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, GameDate DESC, GameID DESC\n) AS top_hitting_sacrificebunts_single_game","compiled":false,"inline":false},{"id":"individual_hitting_season_records","compiledQueryString":"-- Builds individual Clemson hitting season record tables.\n-- Split page-local SQL for pages/individual-records/hitting/index.md.\n-- Filter this query in the page with RecordKey to retrieve each record table.\n\nSELECT *, '/player-profiles/' || PlayerName AS link\nFROM (\nSELECT 'top_hitting_hr_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_HomeRun) AS HomeRun\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n HomeRun,\n RANK() OVER (ORDER BY HomeRun DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n HomeRun\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_hr_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_hits_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_Hits) AS Hits\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hits,\n RANK() OVER (ORDER BY Hits DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n Hits\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_hits_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_runnersbattedin_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_RunnersBattedIn) AS RunnersBattedIn\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n RunnersBattedIn,\n RANK() OVER (ORDER BY RunnersBattedIn DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n RunnersBattedIn\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_runnersbattedin_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_runs_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_Runs) AS Runs\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Runs,\n RANK() OVER (ORDER BY Runs DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n Runs\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_runs_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_baseonballs_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_BaseOnBalls) AS BaseOnBalls\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n BaseOnBalls,\n RANK() OVER (ORDER BY BaseOnBalls DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n BaseOnBalls\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_baseonballs_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_hitbypitch_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_HitbyPitch) AS HitByPitch\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n HitByPitch,\n RANK() OVER (ORDER BY HitByPitch DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n HitByPitch\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_hitbypitch_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_stolenbases_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_StolenBases) AS StolenBases\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n StolenBases,\n RANK() OVER (ORDER BY StolenBases DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n StolenBases\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_stolenbases_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_atbats_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_AtBats) AS AtBats\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n AtBats,\n RANK() OVER (ORDER BY AtBats DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_atbats_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_doubles_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_Doubles) AS Doubles\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Doubles,\n RANK() OVER (ORDER BY Doubles DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n Doubles\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_doubles_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_triples_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_Triples) AS Triples\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Triples,\n RANK() OVER (ORDER BY Triples DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n Triples\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_triples_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_gamesplayed_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(GamesPlayed, 0)) AS GamesPlayed\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(GamesPlayed, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n GamesPlayed,\n RANK() OVER (ORDER BY GamesPlayed DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n GamesPlayed\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_gamesplayed_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_gamesstarted_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(GamesStarted, 0)) AS GamesStarted\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(GamesStarted, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n GamesStarted,\n RANK() OVER (ORDER BY GamesStarted DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n GamesStarted\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_gamesstarted_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_ba_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_AtBats, 0)) AS AtBats,\n ROUND(1.0 * SUM(COALESCE(Hitter_Hits, 0))\n / NULLIF(SUM(COALESCE(Hitter_AtBats, 0)), 0), 3) AS BA\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) > 100\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hits,\n AtBats,\n BA,\n RANK() OVER (ORDER BY BA DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n BA,\n Hits,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_ba_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_slg_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(Hitter_AtBats, 0)) AS AtBats,\n ROUND(1.0 * (SUM(COALESCE(Hitter_Hits, 0)) - SUM(COALESCE(Hitter_Doubles, 0)) - SUM(COALESCE(Hitter_Triples, 0)) - SUM(COALESCE(Hitter_HomeRun, 0))\n + 2 * SUM(COALESCE(Hitter_Doubles, 0)) + 3 * SUM(COALESCE(Hitter_Triples, 0)) + 4 * SUM(COALESCE(Hitter_HomeRun, 0)))\n / NULLIF(SUM(COALESCE(Hitter_AtBats, 0)), 0), 3) AS SLG\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) > 100\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hits,\n Doubles,\n Triples,\n HomeRun,\n AtBats,\n SLG,\n RANK() OVER (ORDER BY SLG DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n SLG,\n Hits,\n Doubles,\n Triples,\n HomeRun,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_slg_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_obp_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(Hitter_AtBats, 0)) AS AtBats,\n ROUND(1.0 * (SUM(COALESCE(Hitter_Hits, 0)) + SUM(COALESCE(Hitter_BaseOnBalls, 0)) + SUM(COALESCE(Hitter_HitbyPitch, 0)))\n / NULLIF(SUM(COALESCE(Hitter_AtBats, 0)) + SUM(COALESCE(Hitter_BaseOnBalls, 0)) + SUM(COALESCE(Hitter_HitbyPitch, 0)) + SUM(COALESCE(Hitter_SacrificeFlies, 0)), 0), 3) AS OBP\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) > 100\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hits,\n BaseOnBalls,\n HitByPitch,\n SacrificeFlies,\n AtBats,\n OBP,\n RANK() OVER (ORDER BY OBP DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n OBP,\n Hits,\n BaseOnBalls,\n HitByPitch,\n SacrificeFlies,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_obp_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_totalbases_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(Hitter_Hits, 0))\n - SUM(COALESCE(Hitter_Doubles, 0))\n - SUM(COALESCE(Hitter_Triples, 0))\n - SUM(COALESCE(Hitter_HomeRun, 0)) AS Singles,\n (\n SUM(COALESCE(Hitter_Hits, 0))\n - SUM(COALESCE(Hitter_Doubles, 0))\n - SUM(COALESCE(Hitter_Triples, 0))\n - SUM(COALESCE(Hitter_HomeRun, 0))\n )\n + 2 * SUM(COALESCE(Hitter_Doubles, 0))\n + 3 * SUM(COALESCE(Hitter_Triples, 0))\n + 4 * SUM(COALESCE(Hitter_HomeRun, 0)) AS TotalBases\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hits,\n Singles,\n Doubles,\n Triples,\n HomeRun,\n TotalBases,\n RANK() OVER (ORDER BY TotalBases DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n TotalBases,\n Singles,\n Doubles,\n Triples,\n HomeRun,\n Hits\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_totalbases_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificeflies_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(Hitter_SacrificeFlies, 0)) AS Hitter_SacrificeFlies\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(Hitter_SacrificeFlies, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hitter_SacrificeFlies,\n RANK() OVER (ORDER BY Hitter_SacrificeFlies DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n Hitter_SacrificeFlies\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_sacrificeflies_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificehits_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(Hitter_SacrificeHits, 0)) AS Hitter_SacrificeHits\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(Hitter_SacrificeHits, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hitter_SacrificeHits,\n RANK() OVER (ORDER BY Hitter_SacrificeHits DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n Hitter_SacrificeHits\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_sacrificehits_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificebunts_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(SacrificeBunts) AS Hitter_SacrificeBunts\n FROM (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n )\n WHERE SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(SacrificeBunts) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hitter_SacrificeBunts,\n RANK() OVER (ORDER BY Hitter_SacrificeBunts DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n Hitter_SacrificeBunts\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_sacrificebunts_season\n) AS player_records","inputQueryString":"-- Builds individual Clemson hitting season record tables.\n-- Split page-local SQL for pages/individual-records/hitting/index.md.\n-- Filter this query in the page with RecordKey to retrieve each record table.\n\nSELECT *, '/player-profiles/' || PlayerName AS link\nFROM (\nSELECT 'top_hitting_hr_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_HomeRun) AS HomeRun\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n HomeRun,\n RANK() OVER (ORDER BY HomeRun DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n HomeRun\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_hr_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_hits_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_Hits) AS Hits\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hits,\n RANK() OVER (ORDER BY Hits DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n Hits\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_hits_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_runnersbattedin_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_RunnersBattedIn) AS RunnersBattedIn\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n RunnersBattedIn,\n RANK() OVER (ORDER BY RunnersBattedIn DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n RunnersBattedIn\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_runnersbattedin_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_runs_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_Runs) AS Runs\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Runs,\n RANK() OVER (ORDER BY Runs DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n Runs\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_runs_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_baseonballs_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_BaseOnBalls) AS BaseOnBalls\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n BaseOnBalls,\n RANK() OVER (ORDER BY BaseOnBalls DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n BaseOnBalls\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_baseonballs_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_hitbypitch_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_HitbyPitch) AS HitByPitch\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n HitByPitch,\n RANK() OVER (ORDER BY HitByPitch DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n HitByPitch\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_hitbypitch_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_stolenbases_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_StolenBases) AS StolenBases\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n StolenBases,\n RANK() OVER (ORDER BY StolenBases DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n StolenBases\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_stolenbases_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_atbats_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_AtBats) AS AtBats\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n AtBats,\n RANK() OVER (ORDER BY AtBats DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_atbats_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_doubles_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_Doubles) AS Doubles\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Doubles,\n RANK() OVER (ORDER BY Doubles DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n Doubles\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_doubles_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_triples_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(Hitter_Triples) AS Triples\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Triples,\n RANK() OVER (ORDER BY Triples DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n Triples\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_triples_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_gamesplayed_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(GamesPlayed, 0)) AS GamesPlayed\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(GamesPlayed, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n GamesPlayed,\n RANK() OVER (ORDER BY GamesPlayed DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n GamesPlayed\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_gamesplayed_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_gamesstarted_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(GamesStarted, 0)) AS GamesStarted\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(GamesStarted, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n GamesStarted,\n RANK() OVER (ORDER BY GamesStarted DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n GamesStarted\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_gamesstarted_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_ba_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_AtBats, 0)) AS AtBats,\n ROUND(1.0 * SUM(COALESCE(Hitter_Hits, 0))\n / NULLIF(SUM(COALESCE(Hitter_AtBats, 0)), 0), 3) AS BA\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) > 100\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hits,\n AtBats,\n BA,\n RANK() OVER (ORDER BY BA DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n BA,\n Hits,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_ba_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_slg_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(Hitter_AtBats, 0)) AS AtBats,\n ROUND(1.0 * (SUM(COALESCE(Hitter_Hits, 0)) - SUM(COALESCE(Hitter_Doubles, 0)) - SUM(COALESCE(Hitter_Triples, 0)) - SUM(COALESCE(Hitter_HomeRun, 0))\n + 2 * SUM(COALESCE(Hitter_Doubles, 0)) + 3 * SUM(COALESCE(Hitter_Triples, 0)) + 4 * SUM(COALESCE(Hitter_HomeRun, 0)))\n / NULLIF(SUM(COALESCE(Hitter_AtBats, 0)), 0), 3) AS SLG\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) > 100\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hits,\n Doubles,\n Triples,\n HomeRun,\n AtBats,\n SLG,\n RANK() OVER (ORDER BY SLG DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n SLG,\n Hits,\n Doubles,\n Triples,\n HomeRun,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_slg_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_obp_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(Hitter_AtBats, 0)) AS AtBats,\n ROUND(1.0 * (SUM(COALESCE(Hitter_Hits, 0)) + SUM(COALESCE(Hitter_BaseOnBalls, 0)) + SUM(COALESCE(Hitter_HitbyPitch, 0)))\n / NULLIF(SUM(COALESCE(Hitter_AtBats, 0)) + SUM(COALESCE(Hitter_BaseOnBalls, 0)) + SUM(COALESCE(Hitter_HitbyPitch, 0)) + SUM(COALESCE(Hitter_SacrificeFlies, 0)), 0), 3) AS OBP\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) > 100\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hits,\n BaseOnBalls,\n HitByPitch,\n SacrificeFlies,\n AtBats,\n OBP,\n RANK() OVER (ORDER BY OBP DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n OBP,\n Hits,\n BaseOnBalls,\n HitByPitch,\n SacrificeFlies,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_obp_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_totalbases_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(Hitter_Hits, 0))\n - SUM(COALESCE(Hitter_Doubles, 0))\n - SUM(COALESCE(Hitter_Triples, 0))\n - SUM(COALESCE(Hitter_HomeRun, 0)) AS Singles,\n (\n SUM(COALESCE(Hitter_Hits, 0))\n - SUM(COALESCE(Hitter_Doubles, 0))\n - SUM(COALESCE(Hitter_Triples, 0))\n - SUM(COALESCE(Hitter_HomeRun, 0))\n )\n + 2 * SUM(COALESCE(Hitter_Doubles, 0))\n + 3 * SUM(COALESCE(Hitter_Triples, 0))\n + 4 * SUM(COALESCE(Hitter_HomeRun, 0)) AS TotalBases\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hits,\n Singles,\n Doubles,\n Triples,\n HomeRun,\n TotalBases,\n RANK() OVER (ORDER BY TotalBases DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n TotalBases,\n Singles,\n Doubles,\n Triples,\n HomeRun,\n Hits\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_totalbases_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificeflies_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(Hitter_SacrificeFlies, 0)) AS Hitter_SacrificeFlies\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(Hitter_SacrificeFlies, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hitter_SacrificeFlies,\n RANK() OVER (ORDER BY Hitter_SacrificeFlies DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n Hitter_SacrificeFlies\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_sacrificeflies_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificehits_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(COALESCE(Hitter_SacrificeHits, 0)) AS Hitter_SacrificeHits\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(COALESCE(Hitter_SacrificeHits, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hitter_SacrificeHits,\n RANK() OVER (ORDER BY Hitter_SacrificeHits DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n Hitter_SacrificeHits\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_sacrificehits_season\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificebunts_season' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n Season,\n SUM(SacrificeBunts) AS Hitter_SacrificeBunts\n FROM (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n )\n WHERE SeasonType IN ${inputs.season.value}\n GROUP BY PlayerName, Season\n HAVING SUM(SacrificeBunts) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Season,\n Hitter_SacrificeBunts,\n RANK() OVER (ORDER BY Hitter_SacrificeBunts DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Season,\n Hitter_SacrificeBunts\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName, Season\n) AS top_hitting_sacrificebunts_season\n) AS player_records","compiled":false,"inline":false},{"id":"individual_hitting_career_records","compiledQueryString":"-- Builds individual Clemson hitting career record tables.\n-- Split page-local SQL for pages/individual-records/hitting/index.md.\n-- Filter this query in the page with RecordKey to retrieve each record table.\n\nSELECT *, '/player-profiles/' || PlayerName AS link\nFROM (\nSELECT 'top_hitting_hr_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_HomeRun) AS HomeRun\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n HomeRun,\n RANK() OVER (ORDER BY HomeRun DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n HomeRun\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_hr_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_hits_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_Hits) AS Hits\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hits,\n RANK() OVER (ORDER BY Hits DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Hits\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_hits_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_runnersbattedin_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_RunnersBattedIn) AS RunnersBattedIn\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n RunnersBattedIn,\n RANK() OVER (ORDER BY RunnersBattedIn DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n RunnersBattedIn\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_runnersbattedin_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_runs_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_Runs) AS Runs\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Runs,\n RANK() OVER (ORDER BY Runs DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Runs\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_runs_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_baseonballs_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_BaseOnBalls) AS BaseOnBalls\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n BaseOnBalls,\n RANK() OVER (ORDER BY BaseOnBalls DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n BaseOnBalls\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_baseonballs_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_hitbypitch_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_HitbyPitch) AS HitByPitch\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n HitByPitch,\n RANK() OVER (ORDER BY HitByPitch DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n HitByPitch\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_hitbypitch_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_stolenbases_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_StolenBases) AS StolenBases\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n ),\n Ranked AS (\n SELECT\n PlayerName,\n StolenBases,\n RANK() OVER (ORDER BY StolenBases DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n StolenBases\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_stolenbases_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_atbats_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_AtBats) AS AtBats\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n AtBats,\n RANK() OVER (ORDER BY AtBats DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_atbats_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_doubles_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_Doubles) AS Doubles\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Doubles,\n RANK() OVER (ORDER BY Doubles DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Doubles\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_doubles_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_triples_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_Triples) AS Triples\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Triples,\n RANK() OVER (ORDER BY Triples DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Triples\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_triples_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_gamesplayed_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(GamesPlayed, 0)) AS GamesPlayed\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(GamesPlayed, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n GamesPlayed,\n RANK() OVER (ORDER BY GamesPlayed DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n GamesPlayed\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_gamesplayed_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_gamesstarted_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(GamesStarted, 0)) AS GamesStarted\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(GamesStarted, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n GamesStarted,\n RANK() OVER (ORDER BY GamesStarted DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n GamesStarted\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_gamesstarted_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_ba_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_AtBats, 0)) AS AtBats,\n ROUND(1.0 * SUM(COALESCE(Hitter_Hits, 0))\n / NULLIF(SUM(COALESCE(Hitter_AtBats, 0)), 0), 3) AS BA\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) > 250\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hits,\n AtBats,\n BA,\n RANK() OVER (ORDER BY BA DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n BA,\n Hits,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_ba_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_slg_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(Hitter_AtBats, 0)) AS AtBats,\n ROUND(1.0 * (SUM(COALESCE(Hitter_Hits, 0)) - SUM(COALESCE(Hitter_Doubles, 0)) - SUM(COALESCE(Hitter_Triples, 0)) - SUM(COALESCE(Hitter_HomeRun, 0))\n + 2 * SUM(COALESCE(Hitter_Doubles, 0)) + 3 * SUM(COALESCE(Hitter_Triples, 0)) + 4 * SUM(COALESCE(Hitter_HomeRun, 0)))\n / NULLIF(SUM(COALESCE(Hitter_AtBats, 0)), 0), 3) AS SLG\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) > 250\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hits,\n Doubles,\n Triples,\n HomeRun,\n AtBats,\n SLG,\n RANK() OVER (ORDER BY SLG DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n SLG,\n Hits,\n Doubles,\n Triples,\n HomeRun,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_slg_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_obp_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(Hitter_AtBats, 0)) AS AtBats,\n ROUND(1.0 * (SUM(COALESCE(Hitter_Hits, 0)) + SUM(COALESCE(Hitter_BaseOnBalls, 0)) + SUM(COALESCE(Hitter_HitbyPitch, 0)))\n / NULLIF(SUM(COALESCE(Hitter_AtBats, 0)) + SUM(COALESCE(Hitter_BaseOnBalls, 0)) + SUM(COALESCE(Hitter_HitbyPitch, 0)) + SUM(COALESCE(Hitter_SacrificeFlies, 0)), 0), 3) AS OBP\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) + SUM(COALESCE(Hitter_BaseOnBalls, 0)) + SUM(COALESCE(Hitter_HitbyPitch, 0)) + SUM(COALESCE(Hitter_SacrificeFlies, 0)) > 250\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hits,\n BaseOnBalls,\n HitByPitch,\n SacrificeFlies,\n AtBats,\n OBP,\n RANK() OVER (ORDER BY OBP DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n OBP,\n Hits,\n BaseOnBalls,\n HitByPitch,\n SacrificeFlies,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_obp_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_totalbases_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(Hitter_Hits, 0))\n - SUM(COALESCE(Hitter_Doubles, 0))\n - SUM(COALESCE(Hitter_Triples, 0))\n - SUM(COALESCE(Hitter_HomeRun, 0)) AS Singles,\n (\n SUM(COALESCE(Hitter_Hits, 0))\n - SUM(COALESCE(Hitter_Doubles, 0))\n - SUM(COALESCE(Hitter_Triples, 0))\n - SUM(COALESCE(Hitter_HomeRun, 0))\n )\n + 2 * SUM(COALESCE(Hitter_Doubles, 0))\n + 3 * SUM(COALESCE(Hitter_Triples, 0))\n + 4 * SUM(COALESCE(Hitter_HomeRun, 0)) AS TotalBases\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hits,\n Singles,\n Doubles,\n Triples,\n HomeRun,\n TotalBases,\n RANK() OVER (ORDER BY TotalBases DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n TotalBases,\n Singles,\n Doubles,\n Triples,\n HomeRun,\n Hits\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_totalbases_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificeflies_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(Hitter_SacrificeFlies, 0)) AS Hitter_SacrificeFlies\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(Hitter_SacrificeFlies, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hitter_SacrificeFlies,\n RANK() OVER (ORDER BY Hitter_SacrificeFlies DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Hitter_SacrificeFlies\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_sacrificeflies_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificehits_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(Hitter_SacrificeHits, 0)) AS Hitter_SacrificeHits\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(Hitter_SacrificeHits, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hitter_SacrificeHits,\n RANK() OVER (ORDER BY Hitter_SacrificeHits DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Hitter_SacrificeHits\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_sacrificehits_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificebunts_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(SacrificeBunts) AS Hitter_SacrificeBunts\n FROM (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n )\n WHERE SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(SacrificeBunts) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hitter_SacrificeBunts,\n RANK() OVER (ORDER BY Hitter_SacrificeBunts DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Hitter_SacrificeBunts\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_sacrificebunts_career\n) AS player_records","inputQueryString":"-- Builds individual Clemson hitting career record tables.\n-- Split page-local SQL for pages/individual-records/hitting/index.md.\n-- Filter this query in the page with RecordKey to retrieve each record table.\n\nSELECT *, '/player-profiles/' || PlayerName AS link\nFROM (\nSELECT 'top_hitting_hr_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_HomeRun) AS HomeRun\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n HomeRun,\n RANK() OVER (ORDER BY HomeRun DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n HomeRun\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_hr_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_hits_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_Hits) AS Hits\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hits,\n RANK() OVER (ORDER BY Hits DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Hits\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_hits_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_runnersbattedin_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_RunnersBattedIn) AS RunnersBattedIn\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n RunnersBattedIn,\n RANK() OVER (ORDER BY RunnersBattedIn DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n RunnersBattedIn\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_runnersbattedin_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_runs_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_Runs) AS Runs\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Runs,\n RANK() OVER (ORDER BY Runs DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Runs\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_runs_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_baseonballs_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_BaseOnBalls) AS BaseOnBalls\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n BaseOnBalls,\n RANK() OVER (ORDER BY BaseOnBalls DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n BaseOnBalls\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_baseonballs_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_hitbypitch_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_HitbyPitch) AS HitByPitch\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n HitByPitch,\n RANK() OVER (ORDER BY HitByPitch DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n HitByPitch\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_hitbypitch_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_stolenbases_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_StolenBases) AS StolenBases\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n ),\n Ranked AS (\n SELECT\n PlayerName,\n StolenBases,\n RANK() OVER (ORDER BY StolenBases DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n StolenBases\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_stolenbases_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_atbats_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_AtBats) AS AtBats\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n AtBats,\n RANK() OVER (ORDER BY AtBats DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_atbats_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_doubles_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_Doubles) AS Doubles\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Doubles,\n RANK() OVER (ORDER BY Doubles DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Doubles\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_doubles_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_triples_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(Hitter_Triples) AS Triples\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(Hitter_AtBats) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Triples,\n RANK() OVER (ORDER BY Triples DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Triples\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_triples_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_gamesplayed_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(GamesPlayed, 0)) AS GamesPlayed\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(GamesPlayed, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n GamesPlayed,\n RANK() OVER (ORDER BY GamesPlayed DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n GamesPlayed\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_gamesplayed_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_gamesstarted_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(GamesStarted, 0)) AS GamesStarted\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(GamesStarted, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n GamesStarted,\n RANK() OVER (ORDER BY GamesStarted DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n GamesStarted\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_gamesstarted_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_ba_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_AtBats, 0)) AS AtBats,\n ROUND(1.0 * SUM(COALESCE(Hitter_Hits, 0))\n / NULLIF(SUM(COALESCE(Hitter_AtBats, 0)), 0), 3) AS BA\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) > 250\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hits,\n AtBats,\n BA,\n RANK() OVER (ORDER BY BA DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n BA,\n Hits,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_ba_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_slg_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(Hitter_AtBats, 0)) AS AtBats,\n ROUND(1.0 * (SUM(COALESCE(Hitter_Hits, 0)) - SUM(COALESCE(Hitter_Doubles, 0)) - SUM(COALESCE(Hitter_Triples, 0)) - SUM(COALESCE(Hitter_HomeRun, 0))\n + 2 * SUM(COALESCE(Hitter_Doubles, 0)) + 3 * SUM(COALESCE(Hitter_Triples, 0)) + 4 * SUM(COALESCE(Hitter_HomeRun, 0)))\n / NULLIF(SUM(COALESCE(Hitter_AtBats, 0)), 0), 3) AS SLG\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) > 250\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hits,\n Doubles,\n Triples,\n HomeRun,\n AtBats,\n SLG,\n RANK() OVER (ORDER BY SLG DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n SLG,\n Hits,\n Doubles,\n Triples,\n HomeRun,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_slg_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_obp_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_BaseOnBalls, 0)) AS BaseOnBalls,\n SUM(COALESCE(Hitter_HitbyPitch, 0)) AS HitByPitch,\n SUM(COALESCE(Hitter_SacrificeFlies, 0)) AS SacrificeFlies,\n SUM(COALESCE(Hitter_AtBats, 0)) AS AtBats,\n ROUND(1.0 * (SUM(COALESCE(Hitter_Hits, 0)) + SUM(COALESCE(Hitter_BaseOnBalls, 0)) + SUM(COALESCE(Hitter_HitbyPitch, 0)))\n / NULLIF(SUM(COALESCE(Hitter_AtBats, 0)) + SUM(COALESCE(Hitter_BaseOnBalls, 0)) + SUM(COALESCE(Hitter_HitbyPitch, 0)) + SUM(COALESCE(Hitter_SacrificeFlies, 0)), 0), 3) AS OBP\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) + SUM(COALESCE(Hitter_BaseOnBalls, 0)) + SUM(COALESCE(Hitter_HitbyPitch, 0)) + SUM(COALESCE(Hitter_SacrificeFlies, 0)) > 250\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hits,\n BaseOnBalls,\n HitByPitch,\n SacrificeFlies,\n AtBats,\n OBP,\n RANK() OVER (ORDER BY OBP DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n OBP,\n Hits,\n BaseOnBalls,\n HitByPitch,\n SacrificeFlies,\n AtBats\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_obp_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_totalbases_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(Hitter_Hits, 0)) AS Hits,\n SUM(COALESCE(Hitter_Doubles, 0)) AS Doubles,\n SUM(COALESCE(Hitter_Triples, 0)) AS Triples,\n SUM(COALESCE(Hitter_HomeRun, 0)) AS HomeRun,\n SUM(COALESCE(Hitter_Hits, 0))\n - SUM(COALESCE(Hitter_Doubles, 0))\n - SUM(COALESCE(Hitter_Triples, 0))\n - SUM(COALESCE(Hitter_HomeRun, 0)) AS Singles,\n (\n SUM(COALESCE(Hitter_Hits, 0))\n - SUM(COALESCE(Hitter_Doubles, 0))\n - SUM(COALESCE(Hitter_Triples, 0))\n - SUM(COALESCE(Hitter_HomeRun, 0))\n )\n + 2 * SUM(COALESCE(Hitter_Doubles, 0))\n + 3 * SUM(COALESCE(Hitter_Triples, 0))\n + 4 * SUM(COALESCE(Hitter_HomeRun, 0)) AS TotalBases\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(Hitter_AtBats, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hits,\n Singles,\n Doubles,\n Triples,\n HomeRun,\n TotalBases,\n RANK() OVER (ORDER BY TotalBases DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n TotalBases,\n Singles,\n Doubles,\n Triples,\n HomeRun,\n Hits\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_totalbases_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificeflies_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(Hitter_SacrificeFlies, 0)) AS Hitter_SacrificeFlies\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(Hitter_SacrificeFlies, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hitter_SacrificeFlies,\n RANK() OVER (ORDER BY Hitter_SacrificeFlies DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Hitter_SacrificeFlies\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_sacrificeflies_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificehits_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(COALESCE(Hitter_SacrificeHits, 0)) AS Hitter_SacrificeHits\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1\n AND SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(COALESCE(Hitter_SacrificeHits, 0)) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hitter_SacrificeHits,\n RANK() OVER (ORDER BY Hitter_SacrificeHits DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Hitter_SacrificeHits\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_sacrificehits_career\n\nUNION ALL BY NAME\n\nSELECT 'top_hitting_sacrificebunts_career' AS RecordKey, *\nFROM (\n WITH Base AS (\n SELECT\n PlayerName,\n SUM(SacrificeBunts) AS Hitter_SacrificeBunts\n FROM (\n WITH sacrifice_bunt_plays AS (\n SELECT\n p.*,\n TRIM(regexp_extract(p.Batter, '^([^,]+)', 1)) AS BatterLastName,\n NULLIF(TRIM(regexp_extract(p.Batter, ',[[:space:]]*([A-Za-z])', 1)), '') AS BatterFirstInitial\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_play_by_play AS p\n WHERE p.InningNumber IS NOT NULL\n AND (\n LOWER(COALESCE(p.PlayDescription, '')) LIKE '%sac, bunt%'\n OR LOWER(COALESCE(p.PlayDescription, '')) LIKE '%bunt, sac%'\n )\n AND (\n (UPPER(p.HomeTeamName) = 'CLEMSON' AND p.BatterTeam = p.HomeTeamID)\n OR (UPPER(p.VisitorTeamName) = 'CLEMSON' AND p.BatterTeam = p.VisitingTeamID)\n )\n ),\n matched_sacrifice_bunts AS (\n SELECT *\n FROM (\n SELECT\n COALESCE(ps.PlayerName, p.Batter) AS PlayerName,\n p.GameID,\n p.Season,\n p.SeasonType,\n ROW_NUMBER() OVER (\n PARTITION BY p.ID\n ORDER BY CASE WHEN ps.PlayerName IS NULL THEN 1 ELSE 0 END, ps.PlayerName\n ) AS MatchRank\n FROM sacrifice_bunt_plays AS p\n LEFT JOIN SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game AS ps\n ON ps.IsClemson = 1\n AND ps.GameID = p.GameID\n AND (\n LOWER(ps.LastName) = LOWER(p.BatterLastName)\n OR LOWER(ps.LastName) LIKE LOWER(p.BatterLastName) || '%'\n OR LOWER(p.BatterLastName) LIKE LOWER(ps.LastName) || '%'\n )\n AND (\n p.BatterFirstInitial IS NULL\n OR LOWER(LEFT(ps.FirstName, 1)) = LOWER(p.BatterFirstInitial)\n )\n )\n WHERE MatchRank = 1\n )\n SELECT\n PlayerName,\n GameID,\n MAX(Season) AS Season,\n MAX(SeasonType) AS SeasonType,\n COUNT(*) AS SacrificeBunts\n FROM matched_sacrifice_bunts\n GROUP BY PlayerName, GameID\n )\n WHERE SeasonType IN ${inputs.career.value}\n GROUP BY PlayerName\n HAVING SUM(SacrificeBunts) > 0\n ),\n Ranked AS (\n SELECT\n PlayerName,\n Hitter_SacrificeBunts,\n RANK() OVER (ORDER BY Hitter_SacrificeBunts DESC) AS Rank\n FROM Base\n )\n SELECT\n Rank,\n PlayerName,\n Hitter_SacrificeBunts\n FROM Ranked\n WHERE Rank <= 10\n ORDER BY Rank, PlayerName\n) AS top_hitting_sacrificebunts_career\n) AS player_records","compiled":false,"inline":false},{"id":"individual_hitting_streak_records","compiledQueryString":"-- Builds individual Clemson hitting streak record tables.\n-- Split page-local SQL for pages/individual-records/hitting/index.md.\n-- Filter this query in the page with RecordKey to retrieve each record table.\n\nSELECT *, '/player-profiles/' || PlayerName AS link\nFROM (\nSELECT 'hit_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_Hits) AS Hits\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND Hitter_AtBats > 0 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN Hits > 0 THEN 1 ELSE 0 END AS HadHit FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadHit,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadHit ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadHit, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadHit, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadHit = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS hit_streak\n\nUNION ALL BY NAME\n\nSELECT 'multi_hit_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_Hits) AS Hits\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND Hitter_AtBats > 0 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN Hits >= 2 THEN 1 ELSE 0 END AS HadMulti FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadMulti,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadMulti ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadMulti, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadMulti, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadMulti = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS multi_hit_streak\n\nUNION ALL BY NAME\n\nSELECT 'rbi_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_RunnersBattedIn) AS RBI\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN RBI > 0 THEN 1 ELSE 0 END AS HadRBI FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadRBI,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadRBI ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadRBI, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadRBI, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadRBI = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS rbi_streak\n\nUNION ALL BY NAME\n\nSELECT 'hr_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_HomeRun) AS HR\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN HR > 0 THEN 1 ELSE 0 END AS HadHR FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadHR,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadHR ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadHR, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadHR, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadHR = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS hr_streak\n\nUNION ALL BY NAME\n\nSELECT 'multi_hr_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_HomeRun) AS HR\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN HR >= 2 THEN 1 ELSE 0 END AS HadMultiHR FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadMultiHR,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadMultiHR ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadMultiHR, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadMultiHR, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadMultiHR = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS multi_hr_streak\n\nUNION ALL BY NAME\n\nSELECT 'sb_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_StolenBases) AS SB\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN SB > 0 THEN 1 ELSE 0 END AS HadSB FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadSB,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadSB ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadSB, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadSB, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadSB = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS sb_streak\n\nUNION ALL BY NAME\n\nSELECT 'multi_sb_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_StolenBases) AS SB\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN SB >= 2 THEN 1 ELSE 0 END AS HadMultiSB FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadMultiSB,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadMultiSB ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadMultiSB, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadMultiSB, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadMultiSB = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS multi_sb_streak\n\nUNION ALL BY NAME\n\nSELECT 'runs_scored_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_Runs) AS Runs\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN Runs > 0 THEN 1 ELSE 0 END AS HadRun FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadRun,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadRun ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadRun, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadRun, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadRun = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS runs_scored_streak\n\nUNION ALL BY NAME\n\nSELECT 'on_base_by_game_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate,\n SUM(Hitter_Hits) + SUM(Hitter_BaseOnBalls) + SUM(Hitter_HitbyPitch) AS TimesReached\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN TimesReached > 0 THEN 1 ELSE 0 END AS Reached FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, Reached,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, Reached ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, Reached, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, Reached, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE Reached = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS on_base_by_game_streak\n) AS player_records","inputQueryString":"-- Builds individual Clemson hitting streak record tables.\n-- Split page-local SQL for pages/individual-records/hitting/index.md.\n-- Filter this query in the page with RecordKey to retrieve each record table.\n\nSELECT *, '/player-profiles/' || PlayerName AS link\nFROM (\nSELECT 'hit_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_Hits) AS Hits\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND Hitter_AtBats > 0 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN Hits > 0 THEN 1 ELSE 0 END AS HadHit FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadHit,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadHit ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadHit, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadHit, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadHit = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS hit_streak\n\nUNION ALL BY NAME\n\nSELECT 'multi_hit_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_Hits) AS Hits\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND Hitter_AtBats > 0 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN Hits >= 2 THEN 1 ELSE 0 END AS HadMulti FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadMulti,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadMulti ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadMulti, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadMulti, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadMulti = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS multi_hit_streak\n\nUNION ALL BY NAME\n\nSELECT 'rbi_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_RunnersBattedIn) AS RBI\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN RBI > 0 THEN 1 ELSE 0 END AS HadRBI FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadRBI,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadRBI ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadRBI, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadRBI, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadRBI = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS rbi_streak\n\nUNION ALL BY NAME\n\nSELECT 'hr_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_HomeRun) AS HR\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN HR > 0 THEN 1 ELSE 0 END AS HadHR FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadHR,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadHR ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadHR, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadHR, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadHR = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS hr_streak\n\nUNION ALL BY NAME\n\nSELECT 'multi_hr_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_HomeRun) AS HR\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN HR >= 2 THEN 1 ELSE 0 END AS HadMultiHR FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadMultiHR,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadMultiHR ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadMultiHR, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadMultiHR, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadMultiHR = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS multi_hr_streak\n\nUNION ALL BY NAME\n\nSELECT 'sb_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_StolenBases) AS SB\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN SB > 0 THEN 1 ELSE 0 END AS HadSB FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadSB,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadSB ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadSB, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadSB, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadSB = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS sb_streak\n\nUNION ALL BY NAME\n\nSELECT 'multi_sb_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_StolenBases) AS SB\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN SB >= 2 THEN 1 ELSE 0 END AS HadMultiSB FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadMultiSB,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadMultiSB ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadMultiSB, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadMultiSB, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadMultiSB = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS multi_sb_streak\n\nUNION ALL BY NAME\n\nSELECT 'runs_scored_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate, SUM(Hitter_Runs) AS Runs\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN Runs > 0 THEN 1 ELSE 0 END AS HadRun FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, HadRun,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, HadRun ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, HadRun, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, HadRun, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE HadRun = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS runs_scored_streak\n\nUNION ALL BY NAME\n\nSELECT 'on_base_by_game_streak' AS RecordKey, *\nFROM (\n WITH FilteredGames AS (\n SELECT PlayerName, GameID, GameDate,\n SUM(Hitter_Hits) + SUM(Hitter_BaseOnBalls) + SUM(Hitter_HitbyPitch) AS TimesReached\n FROM SPORTS_INFO_DW_REPORTING.vw_SFTB_player_stats_by_game\n WHERE IsClemson = 1 AND SeasonType IN ${inputs.streak.value}\n GROUP BY PlayerName, GameID, GameDate\n ),\n Log AS (SELECT PlayerName, GameID, GameDate, CASE WHEN TimesReached > 0 THEN 1 ELSE 0 END AS Reached FROM FilteredGames),\n SI AS (SELECT PlayerName, GameID, GameDate, Reached,\n (ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY GameDate, GameID)) -\n (ROW_NUMBER() OVER (PARTITION BY PlayerName, Reached ORDER BY GameDate, GameID)) AS StreakID FROM Log),\n SL AS (SELECT PlayerName, Reached, COUNT(*) AS StreakLength, MIN(GameDate) AS StreakStart, MAX(GameDate) AS StreakEnd FROM SI GROUP BY PlayerName, Reached, StreakID),\n ranked AS (SELECT PlayerName, StreakLength, StreakStart, StreakEnd, RANK() OVER (ORDER BY StreakLength DESC) AS Rank FROM SL WHERE Reached = 1)\n SELECT *,\n CAST(Rank AS VARCHAR) AS DisplayRank\n FROM ranked\n WHERE Rank <= 10\n ORDER BY Rank ASC, PlayerName, StreakStart DESC\n) AS on_base_by_game_streak\n) AS player_records","compiled":false,"inline":false}]}

Hitting Records

Single Game Records

Season Records

Career Records

Streaks