{"queries":[{"id":"season_types","compiledQueryString":"-- Purpose: Defines the shared season-type dropdown options used by record-book pages.\n-- Data flow: Static query consumed by pages that filter all games, regular season,\n-- postseason, ACC tournament, NCAA tournament, WNIT, or AIAW rows.\n-- Output grain: One dropdown option with value, label, and display order.\n-- Maintenance notes: Values must match SeasonType values or page-level special-case logic.\nSELECT 'All' AS value, 'All Games' AS label, 0 AS sort_order\nUNION ALL\nSELECT 'REG', 'Regular Season', 1\nUNION ALL\nSELECT 'POST', 'All Postseason', 2\nUNION ALL\nSELECT 'ACC', 'ACC Tournament', 3\nUNION ALL\nSELECT 'NCAA', 'NCAA Tournament', 4\nUNION ALL\nSELECT 'AIAW', 'AIAW Tournament', 5\nUNION ALL\nSELECT 'NWIT', 'NWIT Tournament', 6\nUNION ALL\nSELECT 'WNIT', 'WNIT Tournament',7\nORDER BY sort_order","inputQueryString":"-- Purpose: Defines the shared season-type dropdown options used by record-book pages.\n-- Data flow: Static query consumed by pages that filter all games, regular season,\n-- postseason, ACC tournament, NCAA tournament, WNIT, or AIAW rows.\n-- Output grain: One dropdown option with value, label, and display order.\n-- Maintenance notes: Values must match SeasonType values or page-level special-case logic.\nSELECT 'All' AS value, 'All Games' AS label, 0 AS sort_order\nUNION ALL\nSELECT 'REG', 'Regular Season', 1\nUNION ALL\nSELECT 'POST', 'All Postseason', 2\nUNION ALL\nSELECT 'ACC', 'ACC Tournament', 3\nUNION ALL\nSELECT 'NCAA', 'NCAA Tournament', 4\nUNION ALL\nSELECT 'AIAW', 'AIAW Tournament', 5\nUNION ALL\nSELECT 'NWIT', 'NWIT Tournament', 6\nUNION ALL\nSELECT 'WNIT', 'WNIT Tournament',7\nORDER BY sort_order","compiled":false,"inline":false},{"id":"opponent_options","compiledQueryString":"-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel","inputQueryString":"-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel","compiled":false,"inline":true},{"id":"selected_team_opponents","compiledQueryString":"-- Selected opponents for single-game and season team records. Empty means all opponents.\nSELECT Opponent\nFROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team.value}\n AND Opponent IN ${inputs.opponent_dropdown_team.value}","inputQueryString":"-- Selected opponents for single-game and season team records. Empty means all opponents.\nSELECT Opponent\nFROM ${opponent_options}\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team.value}\n AND Opponent IN ${inputs.opponent_dropdown_team.value}","compiled":true,"inline":true},{"id":"team_opponent_selection_context","compiledQueryString":"SELECT\n COUNT(*) AS SelectedOpponentCount,\n (\n SELECT COUNT(DISTINCT Opponent)\n FROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\n WHERE Opponent <> '__ALL__'\n ) AS TotalOpponentCount\nFROM (-- Selected opponents for single-game and season team records. Empty means all opponents.\nSELECT Opponent\nFROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team.value}\n AND Opponent IN ${inputs.opponent_dropdown_team.value})","inputQueryString":"SELECT\n COUNT(*) AS SelectedOpponentCount,\n (\n SELECT COUNT(DISTINCT Opponent)\n FROM ${opponent_options}\n WHERE Opponent <> '__ALL__'\n ) AS TotalOpponentCount\nFROM ${selected_team_opponents}","compiled":true,"inline":true},{"id":"selected_team_half_opponents","compiledQueryString":"-- Half records keep their own opponent selection so controls do not collide with\n-- game, quarter, or margin sections.\nSELECT Opponent\nFROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team_half.value}\n AND Opponent IN ${inputs.opponent_dropdown_team_half.value}","inputQueryString":"-- Half records keep their own opponent selection so controls do not collide with\n-- game, quarter, or margin sections.\nSELECT Opponent\nFROM ${opponent_options}\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team_half.value}\n AND Opponent IN ${inputs.opponent_dropdown_team_half.value}","compiled":true,"inline":true},{"id":"team_half_opponent_selection_context","compiledQueryString":"SELECT\n COUNT(*) AS SelectedOpponentCount,\n (\n SELECT COUNT(DISTINCT Opponent)\n FROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\n WHERE Opponent <> '__ALL__'\n ) AS TotalOpponentCount\nFROM (-- Half records keep their own opponent selection so controls do not collide with\n-- game, quarter, or margin sections.\nSELECT Opponent\nFROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team_half.value}\n AND Opponent IN ${inputs.opponent_dropdown_team_half.value})","inputQueryString":"SELECT\n COUNT(*) AS SelectedOpponentCount,\n (\n SELECT COUNT(DISTINCT Opponent)\n FROM ${opponent_options}\n WHERE Opponent <> '__ALL__'\n ) AS TotalOpponentCount\nFROM ${selected_team_half_opponents}","compiled":true,"inline":true},{"id":"selected_team_quarter_opponents","compiledQueryString":"-- Quarter records keep their own opponent selection for the same reason as half records.\nSELECT Opponent\nFROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team_quarter.value}\n AND Opponent IN ${inputs.opponent_dropdown_team_quarter.value}","inputQueryString":"-- Quarter records keep their own opponent selection for the same reason as half records.\nSELECT Opponent\nFROM ${opponent_options}\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team_quarter.value}\n AND Opponent IN ${inputs.opponent_dropdown_team_quarter.value}","compiled":true,"inline":true},{"id":"team_quarter_opponent_selection_context","compiledQueryString":"SELECT\n COUNT(*) AS SelectedOpponentCount,\n (\n SELECT COUNT(DISTINCT Opponent)\n FROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\n WHERE Opponent <> '__ALL__'\n ) AS TotalOpponentCount\nFROM (-- Quarter records keep their own opponent selection for the same reason as half records.\nSELECT Opponent\nFROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team_quarter.value}\n AND Opponent IN ${inputs.opponent_dropdown_team_quarter.value})","inputQueryString":"SELECT\n COUNT(*) AS SelectedOpponentCount,\n (\n SELECT COUNT(DISTINCT Opponent)\n FROM ${opponent_options}\n WHERE Opponent <> '__ALL__'\n ) AS TotalOpponentCount\nFROM ${selected_team_quarter_opponents}","compiled":true,"inline":true},{"id":"selected_margin_opponents","compiledQueryString":"-- Margin records use a separate selected-opponent list because their filters live\n-- in the margin section.\nSELECT Opponent\nFROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_margin.value}\n AND Opponent IN ${inputs.opponent_dropdown_margin.value}","inputQueryString":"-- Margin records use a separate selected-opponent list because their filters live\n-- in the margin section.\nSELECT Opponent\nFROM ${opponent_options}\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_margin.value}\n AND Opponent IN ${inputs.opponent_dropdown_margin.value}","compiled":true,"inline":true},{"id":"team_record_metric_options","compiledQueryString":"-- Defines team game and season metrics. sort_ascending = 1 marks \"fewest\" records\n-- where lower values rank ahead of higher values.\nSELECT *\nFROM (\n VALUES\n ('wins', 'Wins', 0),\n ('losses', 'Losses', 0),\n ('TeamScore', 'Points', 0),\n ('TotalRebounds', 'Rebounds', 0),\n ('OffensiveRebounds', 'Offensive Rebounds', 0),\n ('DefensiveRebounds', 'Defensive Rebounds', 0),\n ('Assists', 'Assists', 0),\n ('Steals', 'Steals', 0),\n ('Blocks', 'Blocks', 0),\n ('ThreePointersMade', 'Three Pointers Made', 0),\n ('FreeThrowsMade', 'Free Throws Made', 0),\n ('Turnovers', 'Turnovers', 0),\n ('PersonalFouls', 'Personal Fouls', 0),\n ('FieldGoalsMade', 'Field Goals Made', 0),\n ('FieldGoalsAttempted', 'Field Goals Attempted', 0),\n ('ThreePointersAttempted', 'Three Pointers Attempted', 0),\n ('FreeThrowsAttempted', 'Free Throws Attempted', 0),\n ('FieldGoalPercentage', 'Field Goal Percentage', 0),\n ('ThreePointerPercentage', 'Three Pointer Percentage', 0),\n ('FreeThrowPercentage', 'Free Throw Percentage', 0),\n ('ReboundMargin', 'Rebound Margin', 0),\n ('ScoreMargin', 'Score Margin', 0),\n ('FewestPointsScored', 'Fewest Points Scored by Clemson', 1),\n ('FewestOpponentPoints', 'Fewest Points Scored by Opponent', 1)\n) AS t(metric_value, metric_label, sort_ascending)","inputQueryString":"-- Defines team game and season metrics. sort_ascending = 1 marks \"fewest\" records\n-- where lower values rank ahead of higher values.\nSELECT *\nFROM (\n VALUES\n ('wins', 'Wins', 0),\n ('losses', 'Losses', 0),\n ('TeamScore', 'Points', 0),\n ('TotalRebounds', 'Rebounds', 0),\n ('OffensiveRebounds', 'Offensive Rebounds', 0),\n ('DefensiveRebounds', 'Defensive Rebounds', 0),\n ('Assists', 'Assists', 0),\n ('Steals', 'Steals', 0),\n ('Blocks', 'Blocks', 0),\n ('ThreePointersMade', 'Three Pointers Made', 0),\n ('FreeThrowsMade', 'Free Throws Made', 0),\n ('Turnovers', 'Turnovers', 0),\n ('PersonalFouls', 'Personal Fouls', 0),\n ('FieldGoalsMade', 'Field Goals Made', 0),\n ('FieldGoalsAttempted', 'Field Goals Attempted', 0),\n ('ThreePointersAttempted', 'Three Pointers Attempted', 0),\n ('FreeThrowsAttempted', 'Free Throws Attempted', 0),\n ('FieldGoalPercentage', 'Field Goal Percentage', 0),\n ('ThreePointerPercentage', 'Three Pointer Percentage', 0),\n ('FreeThrowPercentage', 'Free Throw Percentage', 0),\n ('ReboundMargin', 'Rebound Margin', 0),\n ('ScoreMargin', 'Score Margin', 0),\n ('FewestPointsScored', 'Fewest Points Scored by Clemson', 1),\n ('FewestOpponentPoints', 'Fewest Points Scored by Opponent', 1)\n) AS t(metric_value, metric_label, sort_ascending)","compiled":false,"inline":true},{"id":"selected_team_record_metric","compiledQueryString":"-- Provides the current team metric and page-facing labels for both game and season tables.\nSELECT\n metric_value,\n metric_label,\n sort_ascending,\n 'Single Game ' || metric_label || ' Record' AS single_game_column_title,\n 'Single Season ' || metric_label || ' Record' AS single_season_column_title\nFROM (-- Defines team game and season metrics. sort_ascending = 1 marks \"fewest\" records\n-- where lower values rank ahead of higher values.\nSELECT *\nFROM (\n VALUES\n ('wins', 'Wins', 0),\n ('losses', 'Losses', 0),\n ('TeamScore', 'Points', 0),\n ('TotalRebounds', 'Rebounds', 0),\n ('OffensiveRebounds', 'Offensive Rebounds', 0),\n ('DefensiveRebounds', 'Defensive Rebounds', 0),\n ('Assists', 'Assists', 0),\n ('Steals', 'Steals', 0),\n ('Blocks', 'Blocks', 0),\n ('ThreePointersMade', 'Three Pointers Made', 0),\n ('FreeThrowsMade', 'Free Throws Made', 0),\n ('Turnovers', 'Turnovers', 0),\n ('PersonalFouls', 'Personal Fouls', 0),\n ('FieldGoalsMade', 'Field Goals Made', 0),\n ('FieldGoalsAttempted', 'Field Goals Attempted', 0),\n ('ThreePointersAttempted', 'Three Pointers Attempted', 0),\n ('FreeThrowsAttempted', 'Free Throws Attempted', 0),\n ('FieldGoalPercentage', 'Field Goal Percentage', 0),\n ('ThreePointerPercentage', 'Three Pointer Percentage', 0),\n ('FreeThrowPercentage', 'Free Throw Percentage', 0),\n ('ReboundMargin', 'Rebound Margin', 0),\n ('ScoreMargin', 'Score Margin', 0),\n ('FewestPointsScored', 'Fewest Points Scored by Clemson', 1),\n ('FewestOpponentPoints', 'Fewest Points Scored by Opponent', 1)\n) AS t(metric_value, metric_label, sort_ascending))\nWHERE metric_value = '${inputs.selected_metric.value}'","inputQueryString":"-- Provides the current team metric and page-facing labels for both game and season tables.\nSELECT\n metric_value,\n metric_label,\n sort_ascending,\n 'Single Game ' || metric_label || ' Record' AS single_game_column_title,\n 'Single Season ' || metric_label || ' Record' AS single_season_column_title\nFROM ${team_record_metric_options}\nWHERE metric_value = '${inputs.selected_metric.value}'","compiled":true,"inline":true},{"id":"team_single_game_ranking","compiledQueryString":"-- Builds Clemson single-game team records. RANK preserves ties; sort_ascending controls\n-- whether high or low values are considered record-leading for the selected metric.\nWITH metric_choice AS (\n SELECT *\n FROM (-- Provides the current team metric and page-facing labels for both game and season tables.\nSELECT\n metric_value,\n metric_label,\n sort_ascending,\n 'Single Game ' || metric_label || ' Record' AS single_game_column_title,\n 'Single Season ' || metric_label || ' Record' AS single_season_column_title\nFROM (-- Defines team game and season metrics. sort_ascending = 1 marks \"fewest\" records\n-- where lower values rank ahead of higher values.\nSELECT *\nFROM (\n VALUES\n ('wins', 'Wins', 0),\n ('losses', 'Losses', 0),\n ('TeamScore', 'Points', 0),\n ('TotalRebounds', 'Rebounds', 0),\n ('OffensiveRebounds', 'Offensive Rebounds', 0),\n ('DefensiveRebounds', 'Defensive Rebounds', 0),\n ('Assists', 'Assists', 0),\n ('Steals', 'Steals', 0),\n ('Blocks', 'Blocks', 0),\n ('ThreePointersMade', 'Three Pointers Made', 0),\n ('FreeThrowsMade', 'Free Throws Made', 0),\n ('Turnovers', 'Turnovers', 0),\n ('PersonalFouls', 'Personal Fouls', 0),\n ('FieldGoalsMade', 'Field Goals Made', 0),\n ('FieldGoalsAttempted', 'Field Goals Attempted', 0),\n ('ThreePointersAttempted', 'Three Pointers Attempted', 0),\n ('FreeThrowsAttempted', 'Free Throws Attempted', 0),\n ('FieldGoalPercentage', 'Field Goal Percentage', 0),\n ('ThreePointerPercentage', 'Three Pointer Percentage', 0),\n ('FreeThrowPercentage', 'Free Throw Percentage', 0),\n ('ReboundMargin', 'Rebound Margin', 0),\n ('ScoreMargin', 'Score Margin', 0),\n ('FewestPointsScored', 'Fewest Points Scored by Clemson', 1),\n ('FewestOpponentPoints', 'Fewest Points Scored by Opponent', 1)\n) AS t(metric_value, metric_label, sort_ascending))\nWHERE metric_value = '${inputs.selected_metric.value}')\n),\n\nselection_context AS (\n SELECT *\n FROM (SELECT\n COUNT(*) AS SelectedOpponentCount,\n (\n SELECT COUNT(DISTINCT Opponent)\n FROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\n WHERE Opponent <> '__ALL__'\n ) AS TotalOpponentCount\nFROM (-- Selected opponents for single-game and season team records. Empty means all opponents.\nSELECT Opponent\nFROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team.value}\n AND Opponent IN ${inputs.opponent_dropdown_team.value}))\n),\n\ngame_source AS (\n -- Applies season-type filtering before aggregation so every downstream metric uses\n -- the same game set.\n SELECT\n t.*,\n CASE\n WHEN '${inputs.season_type_button}' IN ('All', 'All Season') THEN 'All'\n WHEN '${inputs.season_type_button}' = 'POST' THEN 'POST'\n ELSE t.SeasonType\n END AS RecordSeasonType\n FROM SPORTS_INFO_DW_REPORTING.vw_WBB_team_stats_by_game t\n WHERE t.IsClemson = 1\n AND (\n '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND t.SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR t.SeasonType = '${inputs.season_type_button}'\n )\n),\n\ngame_stats AS (\n -- Aggregates to one Clemson game. Percentage metrics are recalculated from made/attempt\n -- totals rather than averaging upstream percentages.\n SELECT\n t.Season,\n t.GameDate,\n t.Opponent,\n t.Coach,\n t.RecordSeasonType AS SeasonType,\n sc.SelectedOpponentCount,\n m.metric_value,\n m.sort_ascending,\n\n MAX(t.WinLoss) AS WinLoss,\n MAX(CASE WHEN t.WinLoss = 'W' THEN 1 ELSE 0 END) AS wins,\n MAX(CASE WHEN t.WinLoss = 'L' THEN 1 ELSE 0 END) AS losses,\n\n SUM(t.TotalPoints) AS TeamScore,\n SUM(t.TotalRebounds) AS TotalRebounds,\n SUM(t.OffensiveRebounds) AS OffensiveRebounds,\n SUM(t.DefensiveRebounds) AS DefensiveRebounds,\n SUM(t.Assists) AS Assists,\n SUM(t.Steals) AS Steals,\n SUM(t.Blocks) AS Blocks,\n SUM(t.ThreePointersMade) AS ThreePointersMade,\n SUM(t.FreeThrowsMade) AS FreeThrowsMade,\n SUM(t.Turnovers) AS Turnovers,\n SUM(t.PersonalFouls) AS PersonalFouls,\n SUM(t.FieldGoalsMade) AS FieldGoalsMade,\n SUM(t.FieldGoalsAttempted) AS FieldGoalsAttempted,\n SUM(t.ThreePointersAttempted) AS ThreePointersAttempted,\n SUM(t.FreeThrowsAttempted) AS FreeThrowsAttempted,\n SUM(t.OppTotalRebounds) AS OppTotalRebounds,\n SUM(t.OppTotalPoints) AS OpponentScore,\n\n SUM(t.FieldGoalsMade) * 1.0 / NULLIF(SUM(t.FieldGoalsAttempted), 0) AS FieldGoalPercentage,\n SUM(t.ThreePointersMade) * 1.0 / NULLIF(SUM(t.ThreePointersAttempted), 0) AS ThreePointerPercentage,\n SUM(t.FreeThrowsMade) * 1.0 / NULLIF(SUM(t.FreeThrowsAttempted), 0) AS FreeThrowPercentage,\n\n SUM(t.TotalRebounds) - SUM(t.OppTotalRebounds) AS ReboundMargin,\n SUM(t.ScoreMargin) AS ScoreMargin,\n\n CAST(SUM(t.FieldGoalsMade) AS INT) || '/' || CAST(SUM(t.FieldGoalsAttempted) AS INT) AS FieldGoalRatio,\n CAST(SUM(t.ThreePointersMade) AS INT) || '/' || CAST(SUM(t.ThreePointersAttempted) AS INT) AS ThreePointerRatio,\n CAST(SUM(t.FreeThrowsMade) AS INT) || '/' || CAST(SUM(t.FreeThrowsAttempted) AS INT) AS FreeThrowRatio\n FROM game_source t\n CROSS JOIN selection_context sc\n CROSS JOIN metric_choice m\n WHERE CAST(LEFT(t.Season, 4) AS INT) >= 1976\n AND t.OppTotalPoints > 1\n AND (\n sc.SelectedOpponentCount = 0\n OR sc.SelectedOpponentCount = sc.TotalOpponentCount\n OR t.Opponent IN (\n SELECT Opponent\n FROM (-- Selected opponents for single-game and season team records. Empty means all opponents.\nSELECT Opponent\nFROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team.value}\n AND Opponent IN ${inputs.opponent_dropdown_team.value})\n )\n )\n GROUP BY\n t.Season,\n t.GameDate,\n t.Opponent,\n t.Coach,\n t.RecordSeasonType,\n sc.SelectedOpponentCount,\n m.metric_value,\n m.sort_ascending\n),\n\nmetric_selected AS (\n -- Pulls the chosen metric into one numeric value so the shared ranking expression\n -- can handle all team record types.\n SELECT\n *,\n CASE\n WHEN metric_value = 'wins' THEN wins\n WHEN metric_value = 'losses' THEN losses\n WHEN metric_value = 'TeamScore' THEN TeamScore\n WHEN metric_value = 'TotalRebounds' THEN TotalRebounds\n WHEN metric_value = 'OffensiveRebounds' THEN OffensiveRebounds\n WHEN metric_value = 'DefensiveRebounds' THEN DefensiveRebounds\n WHEN metric_value = 'Assists' THEN Assists\n WHEN metric_value = 'Steals' THEN Steals\n WHEN metric_value = 'Blocks' THEN Blocks\n WHEN metric_value = 'ThreePointersMade' THEN ThreePointersMade\n WHEN metric_value = 'FreeThrowsMade' THEN FreeThrowsMade\n WHEN metric_value = 'Turnovers' THEN Turnovers\n WHEN metric_value = 'PersonalFouls' THEN PersonalFouls\n WHEN metric_value = 'FieldGoalsMade' THEN FieldGoalsMade\n WHEN metric_value = 'FieldGoalsAttempted' THEN FieldGoalsAttempted\n WHEN metric_value = 'ThreePointersAttempted' THEN ThreePointersAttempted\n WHEN metric_value = 'FreeThrowsAttempted' THEN FreeThrowsAttempted\n WHEN metric_value = 'FieldGoalPercentage' AND FieldGoalsAttempted >= 10 THEN FieldGoalPercentage\n WHEN metric_value = 'ThreePointerPercentage' AND ThreePointersAttempted >= 10 THEN ThreePointerPercentage\n WHEN metric_value = 'FreeThrowPercentage' AND FreeThrowsAttempted >= 10 THEN FreeThrowPercentage\n WHEN metric_value = 'ReboundMargin' THEN ReboundMargin\n WHEN metric_value = 'ScoreMargin' THEN ScoreMargin\n WHEN metric_value = 'FewestPointsScored' THEN TeamScore\n WHEN metric_value = 'FewestOpponentPoints' THEN OpponentScore\n END AS SingleGameStatistic\n FROM game_stats\n),\n\nranked AS (\n -- The two CASE expressions let one RANK support both highest-value and fewest-value records.\n SELECT\n *,\n RANK() OVER (\n ORDER BY\n CASE WHEN sort_ascending = 1 THEN SingleGameStatistic END ASC NULLS LAST,\n CASE WHEN sort_ascending = 0 THEN SingleGameStatistic END DESC NULLS LAST\n ) AS TeamGameRanking\n FROM metric_selected\n)\n\nSELECT *\nFROM ranked\nORDER BY TeamGameRanking\nLIMIT 100","inputQueryString":"-- Builds Clemson single-game team records. RANK preserves ties; sort_ascending controls\n-- whether high or low values are considered record-leading for the selected metric.\nWITH metric_choice AS (\n SELECT *\n FROM ${selected_team_record_metric}\n),\n\nselection_context AS (\n SELECT *\n FROM ${team_opponent_selection_context}\n),\n\ngame_source AS (\n -- Applies season-type filtering before aggregation so every downstream metric uses\n -- the same game set.\n SELECT\n t.*,\n CASE\n WHEN '${inputs.season_type_button}' IN ('All', 'All Season') THEN 'All'\n WHEN '${inputs.season_type_button}' = 'POST' THEN 'POST'\n ELSE t.SeasonType\n END AS RecordSeasonType\n FROM SPORTS_INFO_DW_REPORTING.vw_WBB_team_stats_by_game t\n WHERE t.IsClemson = 1\n AND (\n '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND t.SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR t.SeasonType = '${inputs.season_type_button}'\n )\n),\n\ngame_stats AS (\n -- Aggregates to one Clemson game. Percentage metrics are recalculated from made/attempt\n -- totals rather than averaging upstream percentages.\n SELECT\n t.Season,\n t.GameDate,\n t.Opponent,\n t.Coach,\n t.RecordSeasonType AS SeasonType,\n sc.SelectedOpponentCount,\n m.metric_value,\n m.sort_ascending,\n\n MAX(t.WinLoss) AS WinLoss,\n MAX(CASE WHEN t.WinLoss = 'W' THEN 1 ELSE 0 END) AS wins,\n MAX(CASE WHEN t.WinLoss = 'L' THEN 1 ELSE 0 END) AS losses,\n\n SUM(t.TotalPoints) AS TeamScore,\n SUM(t.TotalRebounds) AS TotalRebounds,\n SUM(t.OffensiveRebounds) AS OffensiveRebounds,\n SUM(t.DefensiveRebounds) AS DefensiveRebounds,\n SUM(t.Assists) AS Assists,\n SUM(t.Steals) AS Steals,\n SUM(t.Blocks) AS Blocks,\n SUM(t.ThreePointersMade) AS ThreePointersMade,\n SUM(t.FreeThrowsMade) AS FreeThrowsMade,\n SUM(t.Turnovers) AS Turnovers,\n SUM(t.PersonalFouls) AS PersonalFouls,\n SUM(t.FieldGoalsMade) AS FieldGoalsMade,\n SUM(t.FieldGoalsAttempted) AS FieldGoalsAttempted,\n SUM(t.ThreePointersAttempted) AS ThreePointersAttempted,\n SUM(t.FreeThrowsAttempted) AS FreeThrowsAttempted,\n SUM(t.OppTotalRebounds) AS OppTotalRebounds,\n SUM(t.OppTotalPoints) AS OpponentScore,\n\n SUM(t.FieldGoalsMade) * 1.0 / NULLIF(SUM(t.FieldGoalsAttempted), 0) AS FieldGoalPercentage,\n SUM(t.ThreePointersMade) * 1.0 / NULLIF(SUM(t.ThreePointersAttempted), 0) AS ThreePointerPercentage,\n SUM(t.FreeThrowsMade) * 1.0 / NULLIF(SUM(t.FreeThrowsAttempted), 0) AS FreeThrowPercentage,\n\n SUM(t.TotalRebounds) - SUM(t.OppTotalRebounds) AS ReboundMargin,\n SUM(t.ScoreMargin) AS ScoreMargin,\n\n CAST(SUM(t.FieldGoalsMade) AS INT) || '/' || CAST(SUM(t.FieldGoalsAttempted) AS INT) AS FieldGoalRatio,\n CAST(SUM(t.ThreePointersMade) AS INT) || '/' || CAST(SUM(t.ThreePointersAttempted) AS INT) AS ThreePointerRatio,\n CAST(SUM(t.FreeThrowsMade) AS INT) || '/' || CAST(SUM(t.FreeThrowsAttempted) AS INT) AS FreeThrowRatio\n FROM game_source t\n CROSS JOIN selection_context sc\n CROSS JOIN metric_choice m\n WHERE CAST(LEFT(t.Season, 4) AS INT) >= 1976\n AND t.OppTotalPoints > 1\n AND (\n sc.SelectedOpponentCount = 0\n OR sc.SelectedOpponentCount = sc.TotalOpponentCount\n OR t.Opponent IN (\n SELECT Opponent\n FROM ${selected_team_opponents}\n )\n )\n GROUP BY\n t.Season,\n t.GameDate,\n t.Opponent,\n t.Coach,\n t.RecordSeasonType,\n sc.SelectedOpponentCount,\n m.metric_value,\n m.sort_ascending\n),\n\nmetric_selected AS (\n -- Pulls the chosen metric into one numeric value so the shared ranking expression\n -- can handle all team record types.\n SELECT\n *,\n CASE\n WHEN metric_value = 'wins' THEN wins\n WHEN metric_value = 'losses' THEN losses\n WHEN metric_value = 'TeamScore' THEN TeamScore\n WHEN metric_value = 'TotalRebounds' THEN TotalRebounds\n WHEN metric_value = 'OffensiveRebounds' THEN OffensiveRebounds\n WHEN metric_value = 'DefensiveRebounds' THEN DefensiveRebounds\n WHEN metric_value = 'Assists' THEN Assists\n WHEN metric_value = 'Steals' THEN Steals\n WHEN metric_value = 'Blocks' THEN Blocks\n WHEN metric_value = 'ThreePointersMade' THEN ThreePointersMade\n WHEN metric_value = 'FreeThrowsMade' THEN FreeThrowsMade\n WHEN metric_value = 'Turnovers' THEN Turnovers\n WHEN metric_value = 'PersonalFouls' THEN PersonalFouls\n WHEN metric_value = 'FieldGoalsMade' THEN FieldGoalsMade\n WHEN metric_value = 'FieldGoalsAttempted' THEN FieldGoalsAttempted\n WHEN metric_value = 'ThreePointersAttempted' THEN ThreePointersAttempted\n WHEN metric_value = 'FreeThrowsAttempted' THEN FreeThrowsAttempted\n WHEN metric_value = 'FieldGoalPercentage' AND FieldGoalsAttempted >= 10 THEN FieldGoalPercentage\n WHEN metric_value = 'ThreePointerPercentage' AND ThreePointersAttempted >= 10 THEN ThreePointerPercentage\n WHEN metric_value = 'FreeThrowPercentage' AND FreeThrowsAttempted >= 10 THEN FreeThrowPercentage\n WHEN metric_value = 'ReboundMargin' THEN ReboundMargin\n WHEN metric_value = 'ScoreMargin' THEN ScoreMargin\n WHEN metric_value = 'FewestPointsScored' THEN TeamScore\n WHEN metric_value = 'FewestOpponentPoints' THEN OpponentScore\n END AS SingleGameStatistic\n FROM game_stats\n),\n\nranked AS (\n -- The two CASE expressions let one RANK support both highest-value and fewest-value records.\n SELECT\n *,\n RANK() OVER (\n ORDER BY\n CASE WHEN sort_ascending = 1 THEN SingleGameStatistic END ASC NULLS LAST,\n CASE WHEN sort_ascending = 0 THEN SingleGameStatistic END DESC NULLS LAST\n ) AS TeamGameRanking\n FROM metric_selected\n)\n\nSELECT *\nFROM ranked\nORDER BY TeamGameRanking\nLIMIT 100","compiled":true,"inline":true},{"id":"period_metric_options","compiledQueryString":"-- Shared metric list for half and quarter tables. Period records are limited to scoring\n-- and fewest-scoring variants.\nSELECT *\nFROM (\n VALUES\n ('TeamScore', 'Points', 0),\n ('OpponentScore', 'Opponent Points', 0),\n ('FewestPointsScored', 'Fewest Points Scored by Clemson', 1),\n ('FewestOpponentPoints', 'Fewest Points Scored by Opponent', 1)\n) AS t(metric_value, metric_label, sort_ascending)","inputQueryString":"-- Shared metric list for half and quarter tables. Period records are limited to scoring\n-- and fewest-scoring variants.\nSELECT *\nFROM (\n VALUES\n ('TeamScore', 'Points', 0),\n ('OpponentScore', 'Opponent Points', 0),\n ('FewestPointsScored', 'Fewest Points Scored by Clemson', 1),\n ('FewestOpponentPoints', 'Fewest Points Scored by Opponent', 1)\n) AS t(metric_value, metric_label, sort_ascending)","compiled":false,"inline":true},{"id":"half_options","compiledQueryString":"-- Half filter options; 'All' keeps both halves in the ranking pool.\nSELECT *\nFROM (\n VALUES\n ('All', 'All Halves'),\n ('1', '1st Half'),\n ('2', '2nd Half')\n) AS t(half_value, half_label)","inputQueryString":"-- Half filter options; 'All' keeps both halves in the ranking pool.\nSELECT *\nFROM (\n VALUES\n ('All', 'All Halves'),\n ('1', '1st Half'),\n ('2', '2nd Half')\n) AS t(half_value, half_label)","compiled":false,"inline":true},{"id":"selected_single_game_metric_half","compiledQueryString":"SELECT\n m.metric_value,\n m.metric_label,\n m.sort_ascending,\n h.half_label,\n h.half_value,\n 'No.' AS column_title\nFROM (-- Shared metric list for half and quarter tables. Period records are limited to scoring\n-- and fewest-scoring variants.\nSELECT *\nFROM (\n VALUES\n ('TeamScore', 'Points', 0),\n ('OpponentScore', 'Opponent Points', 0),\n ('FewestPointsScored', 'Fewest Points Scored by Clemson', 1),\n ('FewestOpponentPoints', 'Fewest Points Scored by Opponent', 1)\n) AS t(metric_value, metric_label, sort_ascending)) m\nCROSS JOIN (-- Half filter options; 'All' keeps both halves in the ranking pool.\nSELECT *\nFROM (\n VALUES\n ('All', 'All Halves'),\n ('1', '1st Half'),\n ('2', '2nd Half')\n) AS t(half_value, half_label)) h\nWHERE m.metric_value = '${inputs.selected_metric_half.value}'\n AND h.half_value = '${inputs.selected_half}'","inputQueryString":"SELECT\n m.metric_value,\n m.metric_label,\n m.sort_ascending,\n h.half_label,\n h.half_value,\n 'No.' AS column_title\nFROM ${period_metric_options} m\nCROSS JOIN ${half_options} h\nWHERE m.metric_value = '${inputs.selected_metric_half.value}'\n AND h.half_value = '${inputs.selected_half}'","compiled":true,"inline":true},{"id":"half_ranking","compiledQueryString":"-- Builds team records by half. The selected half and opponent filters are applied before\n-- RANK so ties are calculated only within the visible record pool.\nWITH metric_choice AS (\n SELECT *\n FROM (SELECT\n m.metric_value,\n m.metric_label,\n m.sort_ascending,\n h.half_label,\n h.half_value,\n 'No.' AS column_title\nFROM (-- Shared metric list for half and quarter tables. Period records are limited to scoring\n-- and fewest-scoring variants.\nSELECT *\nFROM (\n VALUES\n ('TeamScore', 'Points', 0),\n ('OpponentScore', 'Opponent Points', 0),\n ('FewestPointsScored', 'Fewest Points Scored by Clemson', 1),\n ('FewestOpponentPoints', 'Fewest Points Scored by Opponent', 1)\n) AS t(metric_value, metric_label, sort_ascending)) m\nCROSS JOIN (-- Half filter options; 'All' keeps both halves in the ranking pool.\nSELECT *\nFROM (\n VALUES\n ('All', 'All Halves'),\n ('1', '1st Half'),\n ('2', '2nd Half')\n) AS t(half_value, half_label)) h\nWHERE m.metric_value = '${inputs.selected_metric_half.value}'\n AND h.half_value = '${inputs.selected_half}')\n),\n\nselection_context_half AS (\n SELECT *\n FROM (SELECT\n COUNT(*) AS SelectedOpponentCount,\n (\n SELECT COUNT(DISTINCT Opponent)\n FROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\n WHERE Opponent <> '__ALL__'\n ) AS TotalOpponentCount\nFROM (-- Half records keep their own opponent selection so controls do not collide with\n-- game, quarter, or margin sections.\nSELECT Opponent\nFROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team_half.value}\n AND Opponent IN ${inputs.opponent_dropdown_team_half.value}))\n),\n\nteam_half_stats AS (\n -- One row per Clemson game/half/opponent after filters. Opponent point filters remove\n -- incomplete period rows.\n SELECT\n t.Season,\n t.GameDate,\n t.Half,\n t.Opponent AS OpponentName,\n\n CASE\n WHEN sc.SelectedOpponentCount = 0 THEN 'All Opponents'\n WHEN sc.SelectedOpponentCount = sc.TotalOpponentCount THEN 'All Opponents'\n WHEN sc.SelectedOpponentCount = 1 THEN ANY_VALUE(t.Opponent)\n ELSE string_agg(DISTINCT t.Opponent, ', ')\n END AS FilteredOpponent,\n\n sc.SelectedOpponentCount,\n m.metric_value,\n m.sort_ascending,\n\n SUM(t.TotalPoints) AS TeamScore,\n SUM(t.OppTotalPoints) AS OpponentScore\n FROM SPORTS_INFO_DW_REPORTING.vw_WBB_team_stats_by_half t\n CROSS JOIN selection_context_half sc\n CROSS JOIN metric_choice m\n WHERE t.IsClemson = 1\n AND CAST(LEFT(t.Season, 4) AS INT) >= 1978\n AND t.OppTotalPoints > 1\n AND (\n '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND t.SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR t.SeasonType = '${inputs.season_type_button}'\n )\n AND (\n '${inputs.selected_half}' = 'All'\n OR t.Half = '${inputs.selected_half}'\n )\n AND (\n sc.SelectedOpponentCount = 0\n OR sc.SelectedOpponentCount = sc.TotalOpponentCount\n OR t.Opponent IN (\n SELECT Opponent\n FROM (-- Half records keep their own opponent selection so controls do not collide with\n-- game, quarter, or margin sections.\nSELECT Opponent\nFROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team_half.value}\n AND Opponent IN ${inputs.opponent_dropdown_team_half.value})\n )\n )\n GROUP BY\n t.Season,\n t.GameDate,\n t.Half,\n sc.SelectedOpponentCount,\n sc.TotalOpponentCount,\n OpponentName,\n m.metric_value,\n m.sort_ascending\n),\n\nmetric_selected AS (\n SELECT\n *,\n CASE\n WHEN metric_value = 'TeamScore' THEN TeamScore\n WHEN metric_value = 'OpponentScore' THEN OpponentScore\n WHEN metric_value = 'FewestPointsScored' THEN TeamScore\n WHEN metric_value = 'FewestOpponentPoints' THEN OpponentScore\n END AS SingleGameHalfStatistic\n FROM team_half_stats\n),\n\nranked AS (\n -- Uses the same ascending/descending rank pattern as full-game team records.\n SELECT\n *,\n RANK() OVER (\n ORDER BY\n CASE WHEN sort_ascending = 1 THEN SingleGameHalfStatistic END ASC NULLS LAST,\n CASE WHEN sort_ascending = 0 THEN SingleGameHalfStatistic END DESC NULLS LAST\n ) AS HalfTeamRanking\n FROM metric_selected\n)\n\nSELECT *\nFROM ranked\nORDER BY HalfTeamRanking","inputQueryString":"-- Builds team records by half. The selected half and opponent filters are applied before\n-- RANK so ties are calculated only within the visible record pool.\nWITH metric_choice AS (\n SELECT *\n FROM ${selected_single_game_metric_half}\n),\n\nselection_context_half AS (\n SELECT *\n FROM ${team_half_opponent_selection_context}\n),\n\nteam_half_stats AS (\n -- One row per Clemson game/half/opponent after filters. Opponent point filters remove\n -- incomplete period rows.\n SELECT\n t.Season,\n t.GameDate,\n t.Half,\n t.Opponent AS OpponentName,\n\n CASE\n WHEN sc.SelectedOpponentCount = 0 THEN 'All Opponents'\n WHEN sc.SelectedOpponentCount = sc.TotalOpponentCount THEN 'All Opponents'\n WHEN sc.SelectedOpponentCount = 1 THEN ANY_VALUE(t.Opponent)\n ELSE string_agg(DISTINCT t.Opponent, ', ')\n END AS FilteredOpponent,\n\n sc.SelectedOpponentCount,\n m.metric_value,\n m.sort_ascending,\n\n SUM(t.TotalPoints) AS TeamScore,\n SUM(t.OppTotalPoints) AS OpponentScore\n FROM SPORTS_INFO_DW_REPORTING.vw_WBB_team_stats_by_half t\n CROSS JOIN selection_context_half sc\n CROSS JOIN metric_choice m\n WHERE t.IsClemson = 1\n AND CAST(LEFT(t.Season, 4) AS INT) >= 1978\n AND t.OppTotalPoints > 1\n AND (\n '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND t.SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR t.SeasonType = '${inputs.season_type_button}'\n )\n AND (\n '${inputs.selected_half}' = 'All'\n OR t.Half = '${inputs.selected_half}'\n )\n AND (\n sc.SelectedOpponentCount = 0\n OR sc.SelectedOpponentCount = sc.TotalOpponentCount\n OR t.Opponent IN (\n SELECT Opponent\n FROM ${selected_team_half_opponents}\n )\n )\n GROUP BY\n t.Season,\n t.GameDate,\n t.Half,\n sc.SelectedOpponentCount,\n sc.TotalOpponentCount,\n OpponentName,\n m.metric_value,\n m.sort_ascending\n),\n\nmetric_selected AS (\n SELECT\n *,\n CASE\n WHEN metric_value = 'TeamScore' THEN TeamScore\n WHEN metric_value = 'OpponentScore' THEN OpponentScore\n WHEN metric_value = 'FewestPointsScored' THEN TeamScore\n WHEN metric_value = 'FewestOpponentPoints' THEN OpponentScore\n END AS SingleGameHalfStatistic\n FROM team_half_stats\n),\n\nranked AS (\n -- Uses the same ascending/descending rank pattern as full-game team records.\n SELECT\n *,\n RANK() OVER (\n ORDER BY\n CASE WHEN sort_ascending = 1 THEN SingleGameHalfStatistic END ASC NULLS LAST,\n CASE WHEN sort_ascending = 0 THEN SingleGameHalfStatistic END DESC NULLS LAST\n ) AS HalfTeamRanking\n FROM metric_selected\n)\n\nSELECT *\nFROM ranked\nORDER BY HalfTeamRanking","compiled":true,"inline":true},{"id":"quarter_options","compiledQueryString":"-- Quarter filter options include regulation quarters and known overtime labels.\nSELECT *\nFROM (\n VALUES\n ('All', 'All Quarter'),\n ('1', '1st Quarter'),\n ('2', '2nd Quarter'),\n ('3', '3rd Quarter'),\n ('4', '4th Quarter'),\n ('1OT', 'Overtime'),\n ('2OT', 'Double Overtime')\n) AS t(quarter_value, quarter_label)","inputQueryString":"-- Quarter filter options include regulation quarters and known overtime labels.\nSELECT *\nFROM (\n VALUES\n ('All', 'All Quarter'),\n ('1', '1st Quarter'),\n ('2', '2nd Quarter'),\n ('3', '3rd Quarter'),\n ('4', '4th Quarter'),\n ('1OT', 'Overtime'),\n ('2OT', 'Double Overtime')\n) AS t(quarter_value, quarter_label)","compiled":false,"inline":true},{"id":"selected_single_game_metric_quarter","compiledQueryString":"SELECT\n m.metric_value,\n m.metric_label,\n m.sort_ascending,\n q.quarter_label,\n q.quarter_value,\n 'No.' AS column_title\nFROM (-- Shared metric list for half and quarter tables. Period records are limited to scoring\n-- and fewest-scoring variants.\nSELECT *\nFROM (\n VALUES\n ('TeamScore', 'Points', 0),\n ('OpponentScore', 'Opponent Points', 0),\n ('FewestPointsScored', 'Fewest Points Scored by Clemson', 1),\n ('FewestOpponentPoints', 'Fewest Points Scored by Opponent', 1)\n) AS t(metric_value, metric_label, sort_ascending)) m\nCROSS JOIN (-- Quarter filter options include regulation quarters and known overtime labels.\nSELECT *\nFROM (\n VALUES\n ('All', 'All Quarter'),\n ('1', '1st Quarter'),\n ('2', '2nd Quarter'),\n ('3', '3rd Quarter'),\n ('4', '4th Quarter'),\n ('1OT', 'Overtime'),\n ('2OT', 'Double Overtime')\n) AS t(quarter_value, quarter_label)) q\nWHERE m.metric_value = '${inputs.selected_metric_quarter.value}'\n AND q.quarter_value = '${inputs.selected_quarter}'","inputQueryString":"SELECT\n m.metric_value,\n m.metric_label,\n m.sort_ascending,\n q.quarter_label,\n q.quarter_value,\n 'No.' AS column_title\nFROM ${period_metric_options} m\nCROSS JOIN ${quarter_options} q\nWHERE m.metric_value = '${inputs.selected_metric_quarter.value}'\n AND q.quarter_value = '${inputs.selected_quarter}'","compiled":true,"inline":true},{"id":"quarter_ranking","compiledQueryString":"-- Builds team records by quarter or overtime period. Quarter data is filtered to the\n-- encoded 2015+ availability window before ranking.\nWITH metric_choice AS (\n SELECT *\n FROM (SELECT\n m.metric_value,\n m.metric_label,\n m.sort_ascending,\n q.quarter_label,\n q.quarter_value,\n 'No.' AS column_title\nFROM (-- Shared metric list for half and quarter tables. Period records are limited to scoring\n-- and fewest-scoring variants.\nSELECT *\nFROM (\n VALUES\n ('TeamScore', 'Points', 0),\n ('OpponentScore', 'Opponent Points', 0),\n ('FewestPointsScored', 'Fewest Points Scored by Clemson', 1),\n ('FewestOpponentPoints', 'Fewest Points Scored by Opponent', 1)\n) AS t(metric_value, metric_label, sort_ascending)) m\nCROSS JOIN (-- Quarter filter options include regulation quarters and known overtime labels.\nSELECT *\nFROM (\n VALUES\n ('All', 'All Quarter'),\n ('1', '1st Quarter'),\n ('2', '2nd Quarter'),\n ('3', '3rd Quarter'),\n ('4', '4th Quarter'),\n ('1OT', 'Overtime'),\n ('2OT', 'Double Overtime')\n) AS t(quarter_value, quarter_label)) q\nWHERE m.metric_value = '${inputs.selected_metric_quarter.value}'\n AND q.quarter_value = '${inputs.selected_quarter}')\n),\n\nselection_context_quarter AS (\n SELECT *\n FROM (SELECT\n COUNT(*) AS SelectedOpponentCount,\n (\n SELECT COUNT(DISTINCT Opponent)\n FROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\n WHERE Opponent <> '__ALL__'\n ) AS TotalOpponentCount\nFROM (-- Quarter records keep their own opponent selection for the same reason as half records.\nSELECT Opponent\nFROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team_quarter.value}\n AND Opponent IN ${inputs.opponent_dropdown_team_quarter.value}))\n),\n\nteam_quarter_stats AS (\n -- One row per Clemson game/quarter/opponent after filters. The selected quarter can\n -- be a specific period or all periods.\n SELECT\n t.Season,\n t.GameDate,\n t.Quarter,\n t.Opponent AS OpponentName,\n\n CASE\n WHEN sc.SelectedOpponentCount = 0 THEN 'All Opponents'\n WHEN sc.SelectedOpponentCount = sc.TotalOpponentCount THEN 'All Opponents'\n WHEN sc.SelectedOpponentCount = 1 THEN ANY_VALUE(t.Opponent)\n ELSE string_agg(DISTINCT t.Opponent, ', ')\n END AS FilteredOpponent,\n\n sc.SelectedOpponentCount,\n m.metric_value,\n m.sort_ascending,\n\n SUM(t.TotalPoints) AS TeamScore,\n SUM(t.OppTotalPoints) AS OpponentScore\n FROM SPORTS_INFO_DW_REPORTING.vw_WBB_team_stats_by_quarter t\n CROSS JOIN selection_context_quarter sc\n CROSS JOIN metric_choice m\n WHERE t.IsClemson = 1\n AND CAST(LEFT(t.Season, 4) AS INT) >= 2015\n AND t.OppTotalPoints > 1\n AND (\n '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND t.SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR t.SeasonType = '${inputs.season_type_button}'\n )\n AND (\n '${inputs.selected_quarter}' = 'All'\n OR t.Quarter = '${inputs.selected_quarter}'\n )\n AND (\n sc.SelectedOpponentCount = 0\n OR sc.SelectedOpponentCount = sc.TotalOpponentCount\n OR t.Opponent IN (\n SELECT Opponent\n FROM (-- Quarter records keep their own opponent selection for the same reason as half records.\nSELECT Opponent\nFROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team_quarter.value}\n AND Opponent IN ${inputs.opponent_dropdown_team_quarter.value})\n )\n )\n GROUP BY\n t.Season,\n t.GameDate,\n t.Quarter,\n sc.SelectedOpponentCount,\n sc.TotalOpponentCount,\n OpponentName,\n m.metric_value,\n m.sort_ascending\n),\n\nmetric_selected AS (\n SELECT\n *,\n CASE\n WHEN metric_value = 'TeamScore' THEN TeamScore\n WHEN metric_value = 'OpponentScore' THEN OpponentScore\n WHEN metric_value = 'FewestPointsScored' THEN TeamScore\n WHEN metric_value = 'FewestOpponentPoints' THEN OpponentScore\n END AS SingleGameQuarterStatistic\n FROM team_quarter_stats\n),\n\nranked AS (\n -- Uses the same ascending/descending rank pattern as full-game team records.\n SELECT\n *,\n RANK() OVER (\n ORDER BY\n CASE WHEN sort_ascending = 1 THEN SingleGameQuarterStatistic END ASC NULLS LAST,\n CASE WHEN sort_ascending = 0 THEN SingleGameQuarterStatistic END DESC NULLS LAST\n ) AS QuarterTeamRanking\n FROM metric_selected\n)\n\nSELECT *\nFROM ranked\nORDER BY QuarterTeamRanking","inputQueryString":"-- Builds team records by quarter or overtime period. Quarter data is filtered to the\n-- encoded 2015+ availability window before ranking.\nWITH metric_choice AS (\n SELECT *\n FROM ${selected_single_game_metric_quarter}\n),\n\nselection_context_quarter AS (\n SELECT *\n FROM ${team_quarter_opponent_selection_context}\n),\n\nteam_quarter_stats AS (\n -- One row per Clemson game/quarter/opponent after filters. The selected quarter can\n -- be a specific period or all periods.\n SELECT\n t.Season,\n t.GameDate,\n t.Quarter,\n t.Opponent AS OpponentName,\n\n CASE\n WHEN sc.SelectedOpponentCount = 0 THEN 'All Opponents'\n WHEN sc.SelectedOpponentCount = sc.TotalOpponentCount THEN 'All Opponents'\n WHEN sc.SelectedOpponentCount = 1 THEN ANY_VALUE(t.Opponent)\n ELSE string_agg(DISTINCT t.Opponent, ', ')\n END AS FilteredOpponent,\n\n sc.SelectedOpponentCount,\n m.metric_value,\n m.sort_ascending,\n\n SUM(t.TotalPoints) AS TeamScore,\n SUM(t.OppTotalPoints) AS OpponentScore\n FROM SPORTS_INFO_DW_REPORTING.vw_WBB_team_stats_by_quarter t\n CROSS JOIN selection_context_quarter sc\n CROSS JOIN metric_choice m\n WHERE t.IsClemson = 1\n AND CAST(LEFT(t.Season, 4) AS INT) >= 2015\n AND t.OppTotalPoints > 1\n AND (\n '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND t.SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR t.SeasonType = '${inputs.season_type_button}'\n )\n AND (\n '${inputs.selected_quarter}' = 'All'\n OR t.Quarter = '${inputs.selected_quarter}'\n )\n AND (\n sc.SelectedOpponentCount = 0\n OR sc.SelectedOpponentCount = sc.TotalOpponentCount\n OR t.Opponent IN (\n SELECT Opponent\n FROM ${selected_team_quarter_opponents}\n )\n )\n GROUP BY\n t.Season,\n t.GameDate,\n t.Quarter,\n sc.SelectedOpponentCount,\n sc.TotalOpponentCount,\n OpponentName,\n m.metric_value,\n m.sort_ascending\n),\n\nmetric_selected AS (\n SELECT\n *,\n CASE\n WHEN metric_value = 'TeamScore' THEN TeamScore\n WHEN metric_value = 'OpponentScore' THEN OpponentScore\n WHEN metric_value = 'FewestPointsScored' THEN TeamScore\n WHEN metric_value = 'FewestOpponentPoints' THEN OpponentScore\n END AS SingleGameQuarterStatistic\n FROM team_quarter_stats\n),\n\nranked AS (\n -- Uses the same ascending/descending rank pattern as full-game team records.\n SELECT\n *,\n RANK() OVER (\n ORDER BY\n CASE WHEN sort_ascending = 1 THEN SingleGameQuarterStatistic END ASC NULLS LAST,\n CASE WHEN sort_ascending = 0 THEN SingleGameQuarterStatistic END DESC NULLS LAST\n ) AS QuarterTeamRanking\n FROM metric_selected\n)\n\nSELECT *\nFROM ranked\nORDER BY QuarterTeamRanking","compiled":true,"inline":true},{"id":"team_season_ranking","compiledQueryString":"-- Builds Clemson season team records from game rows after opponent and season-type filters.\nWITH metric_choice AS (\n SELECT *\n FROM (-- Provides the current team metric and page-facing labels for both game and season tables.\nSELECT\n metric_value,\n metric_label,\n sort_ascending,\n 'Single Game ' || metric_label || ' Record' AS single_game_column_title,\n 'Single Season ' || metric_label || ' Record' AS single_season_column_title\nFROM (-- Defines team game and season metrics. sort_ascending = 1 marks \"fewest\" records\n-- where lower values rank ahead of higher values.\nSELECT *\nFROM (\n VALUES\n ('wins', 'Wins', 0),\n ('losses', 'Losses', 0),\n ('TeamScore', 'Points', 0),\n ('TotalRebounds', 'Rebounds', 0),\n ('OffensiveRebounds', 'Offensive Rebounds', 0),\n ('DefensiveRebounds', 'Defensive Rebounds', 0),\n ('Assists', 'Assists', 0),\n ('Steals', 'Steals', 0),\n ('Blocks', 'Blocks', 0),\n ('ThreePointersMade', 'Three Pointers Made', 0),\n ('FreeThrowsMade', 'Free Throws Made', 0),\n ('Turnovers', 'Turnovers', 0),\n ('PersonalFouls', 'Personal Fouls', 0),\n ('FieldGoalsMade', 'Field Goals Made', 0),\n ('FieldGoalsAttempted', 'Field Goals Attempted', 0),\n ('ThreePointersAttempted', 'Three Pointers Attempted', 0),\n ('FreeThrowsAttempted', 'Free Throws Attempted', 0),\n ('FieldGoalPercentage', 'Field Goal Percentage', 0),\n ('ThreePointerPercentage', 'Three Pointer Percentage', 0),\n ('FreeThrowPercentage', 'Free Throw Percentage', 0),\n ('ReboundMargin', 'Rebound Margin', 0),\n ('ScoreMargin', 'Score Margin', 0),\n ('FewestPointsScored', 'Fewest Points Scored by Clemson', 1),\n ('FewestOpponentPoints', 'Fewest Points Scored by Opponent', 1)\n) AS t(metric_value, metric_label, sort_ascending))\nWHERE metric_value = '${inputs.selected_metric.value}')\n),\n\nselection_context AS (\n SELECT *\n FROM (SELECT\n COUNT(*) AS SelectedOpponentCount,\n (\n SELECT COUNT(DISTINCT Opponent)\n FROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\n WHERE Opponent <> '__ALL__'\n ) AS TotalOpponentCount\nFROM (-- Selected opponents for single-game and season team records. Empty means all opponents.\nSELECT Opponent\nFROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team.value}\n AND Opponent IN ${inputs.opponent_dropdown_team.value}))\n),\n\nseason_team_stats AS (\n -- One row per Clemson season. Percentages are calculated from season made/attempt totals,\n -- and selected opponent labels summarize the filtered opponent set.\n SELECT\n s.Season,\n s.Coach,\n\n CASE\n WHEN sc.SelectedOpponentCount = 0 THEN 'All Opponents'\n WHEN sc.SelectedOpponentCount = sc.TotalOpponentCount THEN 'All Opponents'\n WHEN sc.SelectedOpponentCount = 1 THEN ANY_VALUE(s.Opponent)\n ELSE string_agg(DISTINCT s.Opponent, ', ')\n END AS FilteredOpponent,\n\n sc.SelectedOpponentCount,\n m.metric_value,\n m.sort_ascending,\n\n COUNT(s.GameID) AS games_played,\n SUM(CASE WHEN s.WinLoss = 'W' THEN 1 ELSE 0 END) AS wins,\n SUM(CASE WHEN s.WinLoss = 'L' THEN 1 ELSE 0 END) AS losses,\n\n SUM(s.TotalPoints) AS TeamScore,\n SUM(s.OppTotalPoints) AS OpponentScore,\n SUM(s.TotalRebounds) AS TotalRebounds,\n SUM(s.OffensiveRebounds) AS OffensiveRebounds,\n SUM(s.DefensiveRebounds) AS DefensiveRebounds,\n SUM(s.Assists) AS Assists,\n SUM(s.Steals) AS Steals,\n SUM(s.Blocks) AS Blocks,\n SUM(s.ThreePointersMade) AS ThreePointersMade,\n SUM(s.FreeThrowsMade) AS FreeThrowsMade,\n SUM(s.Turnovers) AS Turnovers,\n SUM(s.PersonalFouls) AS PersonalFouls,\n SUM(s.FieldGoalsMade) AS FieldGoalsMade,\n SUM(s.FieldGoalsAttempted) AS FieldGoalsAttempted,\n SUM(s.ThreePointersAttempted) AS ThreePointersAttempted,\n SUM(s.FreeThrowsAttempted) AS FreeThrowsAttempted,\n SUM(s.OppTotalRebounds) AS OppTotalRebounds,\n\n SUM(s.FieldGoalsMade) * 1.0 / NULLIF(SUM(s.FieldGoalsAttempted), 0) AS FieldGoalPercentage,\n SUM(s.ThreePointersMade) * 1.0 / NULLIF(SUM(s.ThreePointersAttempted), 0) AS ThreePointerPercentage,\n SUM(s.FreeThrowsMade) * 1.0 / NULLIF(SUM(s.FreeThrowsAttempted), 0) AS FreeThrowPercentage,\n\n SUM(s.TotalRebounds) - SUM(s.OppTotalRebounds) AS ReboundMargin,\n SUM(s.ScoreMargin) AS ScoreMargin,\n\n CAST(SUM(s.FieldGoalsMade) AS INT) || '/' || CAST(SUM(s.FieldGoalsAttempted) AS INT) AS FieldGoalRatio,\n CAST(SUM(s.ThreePointersMade) AS INT) || '/' || CAST(SUM(s.ThreePointersAttempted) AS INT) AS ThreePointerRatio,\n CAST(SUM(s.FreeThrowsMade) AS INT) || '/' || CAST(SUM(s.FreeThrowsAttempted) AS INT) AS FreeThrowRatio,\n\n CAST(SUM(CASE WHEN s.WinLoss='W' THEN 1 ELSE 0 END) AS INT) || '-' || CAST(SUM(CASE WHEN s.WinLoss='L' THEN 1 ELSE 0 END) AS INT) AS WinLossRecord\n FROM SPORTS_INFO_DW_REPORTING.vw_WBB_team_stats_by_game s\n CROSS JOIN selection_context sc\n CROSS JOIN metric_choice m\n WHERE s.IsClemson = 1\n AND CAST(LEFT(s.Season, 4) AS INT) >= 1977\n AND (\n '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND s.SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR s.SeasonType = '${inputs.season_type_button}'\n )\n AND (\n sc.SelectedOpponentCount = 0\n OR sc.SelectedOpponentCount = sc.TotalOpponentCount\n OR s.Opponent IN (\n SELECT Opponent\n FROM (-- Selected opponents for single-game and season team records. Empty means all opponents.\nSELECT Opponent\nFROM (-- Shared opponent dropdown. The '__ALL__' row lets each record section detect\n-- unfiltered all-opponent mode.\nSELECT DISTINCT\n Opponent,\n OpponentLabel,\n sort_order\nFROM SPORTS_INFO_DW_REPORTING.opponent_options\nWHERE Opponent = '__ALL__'\n OR '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR SeasonType = '${inputs.season_type_button}'\nORDER BY sort_order, OpponentLabel)\nWHERE Opponent <> '__ALL__'\n AND '__ALL__' NOT IN ${inputs.opponent_dropdown_team.value}\n AND Opponent IN ${inputs.opponent_dropdown_team.value})\n )\n )\n GROUP BY\n s.Season,\n s.Coach,\n sc.SelectedOpponentCount,\n sc.TotalOpponentCount,\n m.metric_value,\n m.sort_ascending\n),\n\nmetric_selected AS (\n SELECT\n *,\n CASE\n WHEN metric_value = 'wins' THEN wins\n WHEN metric_value = 'losses' THEN losses\n WHEN metric_value = 'TeamScore' THEN TeamScore\n WHEN metric_value = 'TotalRebounds' THEN TotalRebounds\n WHEN metric_value = 'OffensiveRebounds' THEN OffensiveRebounds\n WHEN metric_value = 'DefensiveRebounds' THEN DefensiveRebounds\n WHEN metric_value = 'Assists' THEN Assists\n WHEN metric_value = 'Steals' THEN Steals\n WHEN metric_value = 'Blocks' THEN Blocks\n WHEN metric_value = 'ThreePointersMade' THEN ThreePointersMade\n WHEN metric_value = 'FreeThrowsMade' THEN FreeThrowsMade\n WHEN metric_value = 'Turnovers' THEN Turnovers\n WHEN metric_value = 'PersonalFouls' THEN PersonalFouls\n WHEN metric_value = 'FieldGoalsMade' THEN FieldGoalsMade\n WHEN metric_value = 'FieldGoalsAttempted' THEN FieldGoalsAttempted\n WHEN metric_value = 'ThreePointersAttempted' THEN ThreePointersAttempted\n WHEN metric_value = 'FreeThrowsAttempted' THEN FreeThrowsAttempted\n WHEN metric_value = 'FieldGoalPercentage' AND FieldGoalsAttempted >= 10 THEN FieldGoalPercentage\n WHEN metric_value = 'ThreePointerPercentage' AND ThreePointersAttempted >= 10 THEN ThreePointerPercentage\n WHEN metric_value = 'FreeThrowPercentage' AND FreeThrowsAttempted >= 10 THEN FreeThrowPercentage\n WHEN metric_value = 'ReboundMargin' THEN ReboundMargin\n WHEN metric_value = 'ScoreMargin' THEN ScoreMargin\n WHEN metric_value = 'FewestPointsScored' THEN TeamScore\n WHEN metric_value = 'FewestOpponentPoints' THEN OpponentScore\n END AS SeasonTeamStatistic\n FROM season_team_stats\n),\n\nranked AS (\n -- RANK handles ties and follows the selected metric's high-value or low-value direction.\n SELECT\n *,\n RANK() OVER (\n ORDER BY\n CASE WHEN sort_ascending = 1 THEN SeasonTeamStatistic END ASC NULLS LAST,\n CASE WHEN sort_ascending = 0 THEN SeasonTeamStatistic END DESC NULLS LAST\n ) AS TeamSeasonRanking\n FROM metric_selected\n)\n\nSELECT *\nFROM ranked\nORDER BY TeamSeasonRanking\nLIMIT 100","inputQueryString":"-- Builds Clemson season team records from game rows after opponent and season-type filters.\nWITH metric_choice AS (\n SELECT *\n FROM ${selected_team_record_metric}\n),\n\nselection_context AS (\n SELECT *\n FROM ${team_opponent_selection_context}\n),\n\nseason_team_stats AS (\n -- One row per Clemson season. Percentages are calculated from season made/attempt totals,\n -- and selected opponent labels summarize the filtered opponent set.\n SELECT\n s.Season,\n s.Coach,\n\n CASE\n WHEN sc.SelectedOpponentCount = 0 THEN 'All Opponents'\n WHEN sc.SelectedOpponentCount = sc.TotalOpponentCount THEN 'All Opponents'\n WHEN sc.SelectedOpponentCount = 1 THEN ANY_VALUE(s.Opponent)\n ELSE string_agg(DISTINCT s.Opponent, ', ')\n END AS FilteredOpponent,\n\n sc.SelectedOpponentCount,\n m.metric_value,\n m.sort_ascending,\n\n COUNT(s.GameID) AS games_played,\n SUM(CASE WHEN s.WinLoss = 'W' THEN 1 ELSE 0 END) AS wins,\n SUM(CASE WHEN s.WinLoss = 'L' THEN 1 ELSE 0 END) AS losses,\n\n SUM(s.TotalPoints) AS TeamScore,\n SUM(s.OppTotalPoints) AS OpponentScore,\n SUM(s.TotalRebounds) AS TotalRebounds,\n SUM(s.OffensiveRebounds) AS OffensiveRebounds,\n SUM(s.DefensiveRebounds) AS DefensiveRebounds,\n SUM(s.Assists) AS Assists,\n SUM(s.Steals) AS Steals,\n SUM(s.Blocks) AS Blocks,\n SUM(s.ThreePointersMade) AS ThreePointersMade,\n SUM(s.FreeThrowsMade) AS FreeThrowsMade,\n SUM(s.Turnovers) AS Turnovers,\n SUM(s.PersonalFouls) AS PersonalFouls,\n SUM(s.FieldGoalsMade) AS FieldGoalsMade,\n SUM(s.FieldGoalsAttempted) AS FieldGoalsAttempted,\n SUM(s.ThreePointersAttempted) AS ThreePointersAttempted,\n SUM(s.FreeThrowsAttempted) AS FreeThrowsAttempted,\n SUM(s.OppTotalRebounds) AS OppTotalRebounds,\n\n SUM(s.FieldGoalsMade) * 1.0 / NULLIF(SUM(s.FieldGoalsAttempted), 0) AS FieldGoalPercentage,\n SUM(s.ThreePointersMade) * 1.0 / NULLIF(SUM(s.ThreePointersAttempted), 0) AS ThreePointerPercentage,\n SUM(s.FreeThrowsMade) * 1.0 / NULLIF(SUM(s.FreeThrowsAttempted), 0) AS FreeThrowPercentage,\n\n SUM(s.TotalRebounds) - SUM(s.OppTotalRebounds) AS ReboundMargin,\n SUM(s.ScoreMargin) AS ScoreMargin,\n\n CAST(SUM(s.FieldGoalsMade) AS INT) || '/' || CAST(SUM(s.FieldGoalsAttempted) AS INT) AS FieldGoalRatio,\n CAST(SUM(s.ThreePointersMade) AS INT) || '/' || CAST(SUM(s.ThreePointersAttempted) AS INT) AS ThreePointerRatio,\n CAST(SUM(s.FreeThrowsMade) AS INT) || '/' || CAST(SUM(s.FreeThrowsAttempted) AS INT) AS FreeThrowRatio,\n\n CAST(SUM(CASE WHEN s.WinLoss='W' THEN 1 ELSE 0 END) AS INT) || '-' || CAST(SUM(CASE WHEN s.WinLoss='L' THEN 1 ELSE 0 END) AS INT) AS WinLossRecord\n FROM SPORTS_INFO_DW_REPORTING.vw_WBB_team_stats_by_game s\n CROSS JOIN selection_context sc\n CROSS JOIN metric_choice m\n WHERE s.IsClemson = 1\n AND CAST(LEFT(s.Season, 4) AS INT) >= 1977\n AND (\n '${inputs.season_type_button}' IN ('All', 'All Season')\n OR (\n '${inputs.season_type_button}' = 'POST'\n AND s.SeasonType IN ('ACC', 'NCAA', 'AIAW', 'NWIT', 'WNIT')\n )\n OR s.SeasonType = '${inputs.season_type_button}'\n )\n AND (\n sc.SelectedOpponentCount = 0\n OR sc.SelectedOpponentCount = sc.TotalOpponentCount\n OR s.Opponent IN (\n SELECT Opponent\n FROM ${selected_team_opponents}\n )\n )\n GROUP BY\n s.Season,\n s.Coach,\n sc.SelectedOpponentCount,\n sc.TotalOpponentCount,\n m.metric_value,\n m.sort_ascending\n),\n\nmetric_selected AS (\n SELECT\n *,\n CASE\n WHEN metric_value = 'wins' THEN wins\n WHEN metric_value = 'losses' THEN losses\n WHEN metric_value = 'TeamScore' THEN TeamScore\n WHEN metric_value = 'TotalRebounds' THEN TotalRebounds\n WHEN metric_value = 'OffensiveRebounds' THEN OffensiveRebounds\n WHEN metric_value = 'DefensiveRebounds' THEN DefensiveRebounds\n WHEN metric_value = 'Assists' THEN Assists\n WHEN metric_value = 'Steals' THEN Steals\n WHEN metric_value = 'Blocks' THEN Blocks\n WHEN metric_value = 'ThreePointersMade' THEN ThreePointersMade\n WHEN metric_value = 'FreeThrowsMade' THEN FreeThrowsMade\n WHEN metric_value = 'Turnovers' THEN Turnovers\n WHEN metric_value = 'PersonalFouls' THEN PersonalFouls\n WHEN metric_value = 'FieldGoalsMade' THEN FieldGoalsMade\n WHEN metric_value = 'FieldGoalsAttempted' THEN FieldGoalsAttempted\n WHEN metric_value = 'ThreePointersAttempted' THEN ThreePointersAttempted\n WHEN metric_value = 'FreeThrowsAttempted' THEN FreeThrowsAttempted\n WHEN metric_value = 'FieldGoalPercentage' AND FieldGoalsAttempted >= 10 THEN FieldGoalPercentage\n WHEN metric_value = 'ThreePointerPercentage' AND ThreePointersAttempted >= 10 THEN ThreePointerPercentage\n WHEN metric_value = 'FreeThrowPercentage' AND FreeThrowsAttempted >= 10 THEN FreeThrowPercentage\n WHEN metric_value = 'ReboundMargin' THEN ReboundMargin\n WHEN metric_value = 'ScoreMargin' THEN ScoreMargin\n WHEN metric_value = 'FewestPointsScored' THEN TeamScore\n WHEN metric_value = 'FewestOpponentPoints' THEN OpponentScore\n END AS SeasonTeamStatistic\n FROM season_team_stats\n),\n\nranked AS (\n -- RANK handles ties and follows the selected metric's high-value or low-value direction.\n SELECT\n *,\n RANK() OVER (\n ORDER BY\n CASE WHEN sort_ascending = 1 THEN SeasonTeamStatistic END ASC NULLS LAST,\n CASE WHEN sort_ascending = 0 THEN SeasonTeamStatistic END DESC NULLS LAST\n ) AS TeamSeasonRanking\n FROM metric_selected\n)\n\nSELECT *\nFROM ranked\nORDER BY TeamSeasonRanking\nLIMIT 100","compiled":true,"inline":true}]}

Team Records

Team Records

By Half Records

By Quarter Records