Querying AWS Athena

SELECT
  COUNT(*) AS total_queries_last_week
FROM query_executions
WHERE submission_date_time >= DATE_TRUNC('week', CURRENT_TIMESTAMP) - INTERVAL '7' DAY
AND submission_date_time < DATE_TRUNC('week', CURRENT_TIMESTAMP)

SELECT
  ROUND(CAST(COUNT(*) AS DOUBLE) / 30, 2) AS avg_queries_per_day
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY

SELECT
  AVG(data_scanned_size) / POWER(1024.0, 2) AS avg_mb_scanned,
  AVG(data_scanned_size) / POWER(1024.0, 3) AS avg_gb_scanned
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
AND state_enum = 'SUCCEEDED'

SELECT
    CASE
        WHEN data_scanned_size = 0 THEN 'A 0 bytes (metadata only)'
        WHEN data_scanned_size < 1048576 THEN 'B < 1 MB'
        WHEN data_scanned_size < 104857600 THEN 'C 1 MB - 100 MB'
        WHEN data_scanned_size < 1073741824 THEN 'D 100 MB - 1 GB'
        WHEN data_scanned_size < 10737418240 THEN 'E 1 GB - 10 GB'
        WHEN data_scanned_size < 107374182400 THEN 'F 10 GB - 100 GB'
        ELSE 'G > 100 GB'
    END AS scan_size_bucket,
    COUNT(*) AS query_count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct_of_total
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
AND state_enum = 'SUCCEEDED'
GROUP BY 1
ORDER BY 1

SELECT
  principal_arn,
  COUNT(*) AS query_count,
  SUM(data_scanned_size) AS total_bytes_scanned,
  SUM(data_scanned_size) / POWER(1024.0, 3) AS total_gb_scanned,
  AVG(data_scanned_size) / POWER(1024.0, 2) AS avg_mb_per_query
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
AND state_enum = 'SUCCEEDED'
GROUP BY principal_arn
ORDER BY total_bytes_scanned DESC
LIMIT 25

SELECT
  work_group_name,
  CAST(submission_date_time AS DATE) AS query_date,
  COUNT(*) AS query_count,
  SUM(data_scanned_size) / POWER(1024.0, 3) AS total_gb_scanned,
  SUM(dpu_count) AS total_dpus_used
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '90' DAY
GROUP BY work_group_name, CAST(submission_date_time AS DATE)
ORDER BY work_group_name, query_date

SELECT
  DAY_OF_WEEK(submission_date_time) AS dow,
  HOUR(submission_date_time) AS hour_of_day,
  COUNT(*) AS query_count,
  SUM(data_scanned_size) / POWER(1024.0, 3) AS total_gb_scanned
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '28' DAY
GROUP BY DAY_OF_WEEK(submission_date_time), HOUR(submission_date_time)
ORDER BY query_count DESC

SELECT
  work_group_name,
  COUNT(*) AS total_count,
  COUNT(*) FILTER (WHERE reused_previous_result) AS reused_count,
  ROUND(100.0 * COUNT(*) FILTER (WHERE reused_previous_result) / COUNT(*), 2) AS reuse_percentage,
  SUM(data_scanned_size) FILTER (WHERE NOT reused_previous_result) / POWER(1024.0, 3) AS gb_scanned_without_reuse,
  AVG(data_scanned_size) FILTER (WHERE NOT reused_previous_result) * COUNT(*) FILTER (WHERE reused_previous_result) / POWER(1024.0, 3) AS estimated_gb_saved
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
AND state_enum = 'SUCCEEDED'
GROUP BY work_group_name
ORDER BY estimated_gb_saved DESC

SELECT
  work_group_name,
  DATE_TRUNC('week', submission_date_time) AS week_start,
  SUM(dpu_count) AS total_dpus_allocated,
  SUM(dpu_count * COALESCE(total_execution_duration, 0) / 3600000.0) AS dpu_hours,
  COUNT(*) AS query_count
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '90' DAY
GROUP BY work_group_name, DATE_TRUNC('week', submission_date_time)
ORDER BY work_group_name, week_start

SELECT
  accessed_table.catalog_name,
  accessed_table.database_name,
  accessed_table.table_name,
  COUNT(*) AS access_count,
  COUNT(DISTINCT principal_arn) AS distinct_principals
FROM query_executions
CROSS JOIN UNNEST(tables_accessed) AS t(accessed_table)
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
AND state_enum = 'SUCCEEDED'
GROUP BY 1, 2, 3
ORDER BY 4 DESC
LIMIT 50

SELECT
  work_group_name,
  COUNT(*) AS total_queries,
  SUM(data_scanned_size) / POWER(1024, 3) AS total_gb_scanned,
  AVG(data_scanned_size) / POWER(1024, 2) AS avg_mb_per_query,
  ROUND(100.0 * COUNT(*) FILTER (WHERE state_enum = 'FAILED') / COUNT(*), 2) AS failure_rate_pct,
  AVG(COALESCE(total_execution_duration, 0)) / 1000.0 AS avg_duration_sec,
  APPROX_PERCENTILE(COALESCE(total_execution_duration, 0), 0.95) / 1000.0 AS p95_duration_sec,
  COUNT(DISTINCT principal_arn) AS distinct_principals
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
GROUP BY work_group_name
ORDER BY total_queries DESC

SELECT
  CASE
    WHEN LENGTH(query_string) < 100 THEN 'A < 100 chars'
    WHEN LENGTH(query_string) < 500 THEN 'B 100-500 chars'
    WHEN LENGTH(query_string) < 2000 THEN 'C 500-2000 chars'
    WHEN LENGTH(query_string) < 10000 THEN 'D 2000-10000 chars'
    ELSE 'E > 10000 chars'
    END AS query_length_bucket,
  COUNT(*) AS query_count,
  AVG(COALESCE(total_execution_duration, 0)) / 1000.0 AS avg_duration_sec,
  AVG(data_scanned_size) / POWER(1024.0, 2) AS avg_mb_scanned
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
AND statement_subtype_enum = 'SELECT'
GROUP BY 1
ORDER BY 1

SELECT
  work_group_name,
  statement_type_enum,
  statement_subtype_enum,
  COUNT(*) AS query_count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY work_group_name), 2) AS pct_of_work_group
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
GROUP BY work_group_name, statement_type_enum, statement_subtype_enum
ORDER BY work_group_name, query_count DESC

SELECT
  work_group_name,
  COUNT(*) AS query_count,
  APPROX_PERCENTILE(data_scanned_size, 0.5) / POWER(1024, 3) AS p50_gb,
  APPROX_PERCENTILE(data_scanned_size, 0.9) / POWER(1024, 3) AS p90_gb,
  APPROX_PERCENTILE(data_scanned_size, 0.99) / POWER(1024, 3) AS p99_gb,
  MAX(data_scanned_size) / POWER(1024, 3) AS max_gb
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
AND state_enum <> 'FAILED' -- no charge for bytes scanned on FAILED queries
AND statement_type_enum = 'DML'
GROUP BY work_group_name
ORDER BY 5 DESC

WITH monthly AS (
  SELECT
    work_group_name,
    DATE_TRUNC('month', submission_date_time) AS month,
    COUNT(*) AS query_count
  FROM query_executions
  WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '6' MONTH
  GROUP BY work_group_name, DATE_TRUNC('month', submission_date_time)
)
SELECT
  work_group_name,
  month,
  query_count,
  LAG(query_count) OVER (PARTITION BY work_group_name ORDER BY month) AS prev_month_count,
  ROUND(100.0 * (query_count - LAG(query_count) OVER (PARTITION BY work_group_name ORDER BY month)) / LAG(query_count) OVER (PARTITION BY work_group_name ORDER BY month), 2) AS growth_pct
FROM monthly
ORDER BY work_group_name, month

SELECT
  DATE_TRUNC('week', submission_date_time) AS week_start,
  work_group_name,
  COUNT(*) AS total_queries,
  COUNT(*) FILTER (WHERE managed_query_results_enabled) AS managed_results_count,
  ROUND(100.0 * COUNT(*) FILTER (WHERE managed_query_results_enabled) / COUNT(*), 2) AS managed_results_pct
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '90' DAY
GROUP BY DATE_TRUNC('week', submission_date_time), work_group_name
ORDER BY work_group_name, week_start

SELECT
  work_group_name,
  statement_type_enum,
  statement_subtype_enum,
  COUNT(*) AS query_count,
  AVG(data_scanned_size) / POWER(1024.0, 2) AS avg_mb_scanned,
  AVG(COALESCE(total_execution_duration, 0)) / 1000.0 AS avg_duration_sec
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
GROUP BY work_group_name, statement_type_enum, statement_subtype_enum
ORDER BY work_group_name, query_count DESC

SELECT
  query_execution_id,
  work_group_name,
  principal_arn,
  submission_date_time,
  COALESCE(total_execution_duration, 0) / 1000.0 AS total_sec,
  COALESCE(query_queue_time_duration, 0) / 1000.0 AS queue_sec,
  COALESCE(query_planning_duration, 0) / 1000.0 AS planning_sec, -- subset of engine_sec, not additive
  engine_execution_duration / 1000.0 AS engine_sec,
  COALESCE(service_pre_processing_duration, 0) / 1000.0 AS pre_processing_sec,
  COALESCE(service_processing_duration, 0) / 1000.0 AS processing_sec,
  data_scanned_size / POWER(1024.0, 3) AS gb_scanned,
  SUBSTR(query_string, 1, 200) AS query_preview
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
AND state_enum = 'SUCCEEDED'
ORDER BY total_execution_duration DESC
LIMIT 25

SELECT
  query_execution_id,
  work_group_name,
  principal_arn,
  submission_date_time,
  data_scanned_size / POWER(1024, 3) AS gb_scanned,
  COALESCE(total_execution_duration, 0) / 1000.0 AS duration_sec,
  SUBSTR(query_string, 1, 200) AS query_preview
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
AND state_enum = 'SUCCEEDED'
ORDER BY data_scanned_size DESC
LIMIT 25

SELECT
  accessed_table.catalog_name,
  accessed_table.database_name,
  accessed_table.table_name,
  COUNT(*) AS failed_query_count
FROM query_executions
CROSS JOIN UNNEST(tables_accessed) AS t(accessed_table)
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
AND state_enum = 'FAILED'
GROUP BY 1, 2, 3
ORDER BY 4 DESC
LIMIT 25

SELECT
  query_execution_id,
  work_group_name,
  submission_date_time,
  COALESCE(query_planning_duration, 0) / 1000.0 AS planning_sec,
  COALESCE(query_queue_time_duration, 0) / 1000.0 AS queue_sec,
  COALESCE(total_execution_duration, 0) / 1000.0 AS total_sec,
  ROUND(100.0 * COALESCE(query_planning_duration, 0) / NULLIF(COALESCE(total_execution_duration, 0), 0), 2) AS planning_pct_of_total,
  SUBSTR(query_string, 1, 200) AS query_preview
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
AND state_enum = 'SUCCEEDED'
ORDER BY query_planning_duration DESC
LIMIT 25

SELECT
  work_group_name,
  error_category_enum,
  error_type_enum,
  ANY_VALUE(error_message) AS sample_error_message,
  COUNT(*) AS failure_count
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
AND state_enum = 'FAILED'
GROUP BY 1, 2, 3
ORDER BY failure_count DESC
LIMIT 25

SELECT
  work_group_name,
  DATE_TRUNC('week', submission_date_time) AS week_start,
  COUNT(*) AS query_count,
  AVG(COALESCE(total_execution_duration, 0)) / 1000.0 AS avg_duration_sec,
  APPROX_PERCENTILE(COALESCE(total_execution_duration, 0), 0.95) / 1000.0 AS p95_duration_sec,
  AVG(data_scanned_size) / POWER(1024, 2) AS avg_mb_scanned
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '90' DAY
AND state_enum = 'SUCCEEDED'
GROUP BY 1, 2
ORDER BY 1, 2

WITH hourly_queue AS (
  SELECT
    DATE_TRUNC('hour', submission_date_time) AS hour,
    AVG(COALESCE(query_queue_time_duration, 0)) AS avg_queue_ms,
    COUNT(*) AS query_count
  FROM query_executions
  WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '14' DAY
  GROUP BY 1
),
with_baseline AS (
  SELECT
    *,
    AVG(avg_queue_ms) OVER (
      ORDER BY hour
      RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND INTERVAL '1' HOUR PRECEDING
    ) AS rolling_avg_queue_ms
    FROM hourly_queue
)
SELECT
  hour,
  query_count,
  avg_queue_ms / 1000.0 AS avg_queue_sec,
  rolling_avg_queue_ms / 1000.0 AS rolling_avg_queue_sec,
  ROUND(avg_queue_ms / rolling_avg_queue_ms, 2) AS spike_ratio
FROM with_baseline
WHERE avg_queue_ms > 2 * rolling_avg_queue_ms
AND rolling_avg_queue_ms > 0
ORDER BY spike_ratio DESC
LIMIT 25

WITH hourly_errors AS (
  SELECT
    work_group_name,
    DATE_TRUNC('hour', submission_date_time) AS hour,
    COUNT(*) AS total_queries,
    COUNT(*) FILTER (WHERE state_enum = 'FAILED') AS failed_queries,
    100.0 * COUNT(*) FILTER (WHERE state_enum = 'FAILED') / COUNT(*) AS failure_rate
  FROM query_executions
  WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '14' DAY
  GROUP BY work_group_name, DATE_TRUNC('hour', submission_date_time)
),
with_baseline AS (
  SELECT
    *,
    AVG(failure_rate) OVER (
      PARTITION BY work_group_name
      ORDER BY hour
      RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND INTERVAL '1' HOUR PRECEDING
    ) AS rolling_avg_failure_rate
  FROM hourly_errors
)
SELECT
  work_group_name,
  hour,
  total_queries,
  failed_queries,
  ROUND(failure_rate, 2) AS failure_rate_pct,
  ROUND(rolling_avg_failure_rate, 2) AS rolling_avg_pct,
  ROUND(failure_rate / rolling_avg_failure_rate, 2) AS spike_ratio
FROM with_baseline
WHERE failure_rate > 2 * rolling_avg_failure_rate
AND rolling_avg_failure_rate > 0
AND total_queries >= 10  -- ignore low-volume hours
ORDER BY spike_ratio DESC
LIMIT 25

SELECT
  query_execution_id,
  work_group_name,
  principal_arn,
  submission_date_time,
  CARDINALITY(tables_accessed) AS tables_count,
  COALESCE(total_execution_duration, 0) / 1000.0 AS duration_sec,
  data_scanned_size / POWER(1024, 3) AS gb_scanned,
  SUBSTR(query_string, 1, 300) AS query_preview
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
AND state_enum = 'SUCCEEDED'
AND CARDINALITY(tables_accessed) >= 5
ORDER BY CARDINALITY(tables_accessed) DESC, total_execution_duration DESC
LIMIT 25

SELECT
  work_group_name,
  query_execution_id,
  principal_arn,
  submission_date_time,
  dpu_count,
  data_scanned_size / POWER(1024.0, 2) AS mb_scanned,
  engine_execution_duration / 1000.0 AS engine_sec,
  data_scanned_size / POWER(1024.0, 2) / (dpu_count * engine_execution_duration / 1000.0) AS mb_per_dpu_second,
  SUBSTR(query_string, 1, 200) AS query_preview
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
AND state_enum = 'SUCCEEDED'
AND dpu_count > 0
AND data_scanned_size > 0
AND engine_execution_duration > 0
ORDER BY mb_per_dpu_second ASC
LIMIT 25

WITH query_groups AS (
  SELECT
    work_group_name,
    query_string,
    COUNT(*) AS repetition_count,
    MIN(submission_date_time) AS first_run,
    MAX(submission_date_time) AS last_run,
    SUM(data_scanned_size) / POWER(1024.0, 2) AS total_mb_scanned
  FROM query_executions
  WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
  AND state_enum = 'SUCCEEDED'
  AND statement_type_enum = 'DML'
  AND NOT result_reuse_enabled
  AND NOT reused_previous_result
  GROUP BY work_group_name, query_string
  HAVING COUNT(*) >= 3
)
SELECT
  work_group_name,
  repetition_count,
  total_mb_scanned,
  DATE_DIFF('minute', first_run, last_run) AS window_minutes,
  SUBSTR(query_string, 1, 200) AS query_preview
FROM query_groups
ORDER BY repetition_count DESC
LIMIT 25

SELECT 
  submission_date_time,
  principal_arn,
  work_group_name,
  statement_type_enum,
  statement_subtype_enum,
  state_enum,
  tables_accessed,
  SUBSTR(query_string, 1, 500) AS query_preview
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
ORDER BY submission_date_time DESC

SELECT
  query_execution_id,
  principal_arn,
  work_group_name,
  submission_date_time,
  completion_date_time,
  state_enum,
  statement_type_enum,
  data_scanned_size / POWER(1024.0, 2) AS mb_scanned,
  COALESCE(total_execution_duration, 0) / 1000.0 AS duration_sec,
  error_message,
  tables_accessed,
  query_string
FROM query_executions
WHERE query_execution_id = '<query-execution-id>' -- replace with the actual ID

SELECT
  query_execution_id,
  submission_date_time,
  principal_arn,
  work_group_name,
  statement_type_enum,
  accessed_table.catalog_name,
  accessed_table.database_name,
  accessed_table.table_name,
  SUBSTR(query_string, 1, 300) AS query_preview
FROM query_executions
CROSS JOIN UNNEST(tables_accessed) AS t(accessed_table)
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
  AND accessed_table.database_name = 'sensitive_db' -- replace with the database
  AND accessed_table.table_name IN ('pii_table', 'financial_data') -- replace with the tables
ORDER BY submission_date_time DESC

SELECT
  query_execution_id,
  submission_date_time,
  principal_arn,
  work_group_name,
  statement_type_enum,
  SUBSTR(query_string, 1, 200) AS query_preview
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
  AND (
    HOUR(submission_date_time) < 8 -- time is in UTC, change for local time zone
    OR HOUR(submission_date_time) >= 20 -- time is in UTC, change for local time zone
    OR DAY_OF_WEEK(submission_date_time) IN (6, 7)  -- Saturday, Sunday
  )
  AND principal_arn NOT LIKE '%:assumed-role/service-%' -- exclude service roles
  AND principal_arn NOT LIKE '%:assumed-role/lambda-%' -- exclude Lambda
ORDER BY submission_date_time DESC

WITH first_seen AS (
  SELECT
    principal_arn,
    work_group_name,
    MIN(submission_date_time) AS first_query_time,
    COUNT(*) AS total_queries_since
  FROM query_executions
  GROUP BY principal_arn, work_group_name
)
SELECT
  principal_arn,
  work_group_name,
  first_query_time,
  total_queries_since
FROM first_seen
WHERE first_query_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
ORDER BY first_query_time DESC

WITH principal_work_groups AS (
  SELECT
    principal_arn,
    ARRAY_AGG(DISTINCT work_group_name) AS work_groups_used,
    CARDINALITY(ARRAY_AGG(DISTINCT work_group_name)) AS work_group_count
  FROM query_executions
  WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
  GROUP BY principal_arn
)
SELECT
  principal_arn,
  work_group_count,
  work_groups_used
FROM principal_work_groups
WHERE work_group_count > 1
ORDER BY work_group_count DESC

WITH minutely AS (
  SELECT
    principal_arn,
    work_group_name,
    DATE_TRUNC('minute', submission_date_time) AS minute,
    COUNT(*) AS queries_in_minute
  FROM query_executions
  WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
  GROUP BY principal_arn, work_group_name, DATE_TRUNC('minute', submission_date_time)
)
SELECT
  principal_arn,
  work_group_name,
  minute,
  queries_in_minute
FROM minutely
WHERE queries_in_minute > 50 -- adjust threshold as needed
ORDER BY queries_in_minute DESC
LIMIT 25

SELECT
  principal_arn,
  COUNT(DISTINCT accessed_table.catalog_name || '.' || accessed_table.database_name || '.' || accessed_table.table_name) AS distinct_tables,
  COUNT(*) AS total_queries,
  COUNT(DISTINCT accessed_table.database_name) AS distinct_databases
FROM query_executions
CROSS JOIN UNNEST(tables_accessed) AS t(accessed_table)
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
GROUP BY principal_arn
ORDER BY distinct_tables DESC
LIMIT 25

SELECT
  principal_arn,
  work_group_name,
  COUNT(*) AS total_queries,
  COUNT(*) FILTER (WHERE state_enum = 'FAILED') AS failed_queries,
  ROUND(100.0 * COUNT(*) FILTER (WHERE state_enum = 'FAILED') / COUNT(*), 2) AS failure_rate_pct
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
GROUP BY principal_arn, work_group_name
HAVING COUNT(*) >= 10  -- only principals with meaningful volume
ORDER BY failure_rate_pct DESC
LIMIT 25

SELECT
  work_group_name,
  COUNT(*) AS total_queries,
  ROUND(100.0 * COUNT(*) FILTER (WHERE COALESCE(total_execution_duration, 0) <= 5000) / COUNT(*), 2) AS pct_under_5s,
  ROUND(100.0 * COUNT(*) FILTER (WHERE COALESCE(total_execution_duration, 0) <= 30000) / COUNT(*), 2) AS pct_under_30s,
  ROUND(100.0 * COUNT(*) FILTER (WHERE COALESCE(total_execution_duration, 0) <= 60000) / COUNT(*), 2) AS pct_under_60s,
  ROUND(100.0 * COUNT(*) FILTER (WHERE COALESCE(total_execution_duration, 0) <= 300000) / COUNT(*), 2) AS pct_under_5m,
  APPROX_PERCENTILE(COALESCE(total_execution_duration, 0), 0.5) / 1000.0 AS p50_sec,
  APPROX_PERCENTILE(COALESCE(total_execution_duration, 0), 0.99) / 1000.0 AS p99_sec
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
AND state_enum = 'SUCCEEDED'
GROUP BY work_group_name
ORDER BY pct_under_30s ASC