Setting the file. One moment.
Skill 150 · Querying AWS Athena
Subchapter 150.1
references/queries.mdMarkdown26 KBView on GitHub
query_executions — Query LibraryA library of example SQL queries against the Amazon Athena query_executions system table, organized by use case. Read this file when a user needs a specific analysis; the parent SKILL.md carries the core patterns inline.
Query path: "s3tablescatalog/aws-athena"."metadata"."query_executions" (referenced below as query_executions; qualify with the full path when running).
Reminders: always filter on submission_date_time for partition pruning; the table is read-only (no INSERT/UPDATE/DELETE); only completed queries (SUCCEEDED, FAILED, CANCELLED) are present. Queries projecting query_string or principal_arn output sensitive data — confirm the query result location is SSE-KMS-encrypted and access-controlled before running. Pass untrusted values (query execution IDs, database/table names) as Athena execution parameters (? placeholders) rather than string-interpolating them, to avoid SQL injection into a table that holds sensitive text.
Query volume over time — How many queries did we run last week:
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)Average query volume — How many queries do we run on average per day:
SELECT
ROUND(CAST(COUNT(*) AS DOUBLE) / 30, 2) AS avg_queries_per_day
FROM query_executions
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAYAverage query data scanned — How much data do queries scan on average:
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'What is the distribution of data scanned by queries? — Bucket queries into size ranges to understand the scan distribution. This histogram pattern (CASE size buckets, or GROUP BY a discrete numeric column, plus SUM(COUNT(*)) OVER () for percent of total) generalizes to any numeric column — for example, GROUP BY dpu_count for a DPU-usage distribution, or bucket LENGTH(query_string) for query complexity:
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 1Top data scanners — Identify which principals (users) scan the most data over the past 30 days:
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 25Query volume, data scanned, and DPU usage trends by work group — Daily query count and total data scanned per work group. Is our usage increasing or decreasing? What work groups are increasing in query volume? What work groups are increasing in DPU usage:
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_datePeak usage hours — Query volume by hour of day and day of week to identify peak periods. Using 28 days to capture exactly four weeks. At what time of day do we run the most queries:
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 DESCResult reuse savings — How much data scanned was avoided thanks to result reuse:
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 DESCDPU consumption by work_group — Total DPU-hours consumed per work_group for capacity analysis:
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_startMost queried tables — Which tables are accessed most frequently:
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 50Work group comparison — Side-by-side comparison of work groups on key metrics: volume, average data scanned, failure rate, and average duration:
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 DESCQuery complexity — Histogram of query text length of SELECT queries. This can be used to identify generated or overly complex SQL:
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 1Statement type breakdown — Proportion of statement type (DDL, DML, UTILITY) and substatement type (SELECT, ALTER TABLE ADD PARTITION, INSERT) per work group:
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 DESCData scanned percentiles by work group — P50/P90/P99 data scanned per query to understand tail cost drivers:
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 DESCWork group growth rate — Month-over-month growth in query count per work group:
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, monthManaged query results adoption over time — Track adoption of managed query results week over week:
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_startSubstatement type distribution — Breakdown of substatement types to understand actual workload composition. Substatement type examples are SELECT, CREATE_TABLE_AS_SELECT, UNLOAD, CREATE_TABLE, ALTER_TABLE_ADD_PARTITION:
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 DESCSlowest queries with time breakdown — Top queries by total execution time. total_sec = queue + pre_processing + engine + processing; planning_sec is part of engine_sec, so do not add it separately:
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 25Which queries scanned the most data? — Top queries ranked by data scanned:
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 25Which tables result in the most failed queries? — Tables most frequently accessed by queries that fail:
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 25Planning time outliers — Queries with the longest planning time. This can be caused by queries that need to list many partitions on S3:
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 25Failed query patterns — Most common error categories and messages grouped by work group:
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 25Average execution time trend by work group — Detect performance regressions by tracking weekly avg/p95 execution time:
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, 2Queue spike detection — Hours where average queue time exceeds 2x the rolling average. This indicates periods where the full account quota is used:
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 25Error spike detection — Hours where failure rate exceeds 2x the rolling average:
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 25Multi-table join complexity — Queries accessing 5+ tables in a single execution — candidates for optimization or materialization:
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 25DPU efficiency — Data scanned per DPU-second — low ratios may indicate over-provisioned queries:
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 25Repeated identical queries without result reuse — Same query text run multiple times in a short window without result reuse enabled. Either query result reuse was not enabled on these queries, or the time window was too short:
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 25Query audit trail — All queries with who ran them, when, and on which tables:
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 DESCFind the principal that ran a specific query — Look up a specific query execution by ID and find out who ran it, and in what work group:
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 IDSensitive table access log — All queries touching specific sensitive tables. Replace the filter values with the target database and table names:
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 DESCAfter-hours activity — Queries submitted outside business hours by human principals (not service roles):
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 DESCNew principal detection — First-ever query per principal (user) — surface new identities appearing in work groups. This query intentionally omits the submission_date_time partition filter in the inner CTE — finding a principal’s true first appearance requires scanning all history, so it is the deliberate exception to the partition-pruning rule and can be expensive on large tables:
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 DESCWork group access patterns — Which principals are active in which work groups — detect unexpected cross-work group usage:
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 DESCQuery submission burst detection — Principals submitting more than N queries within a 1-minute window, possibly indicating runaway automation:
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 25Table access breadth per principal — Number of distinct tables accessed per principal over 30 days — unusually high breadth may warrant review:
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 25Failed query ratio by principal — Principals with high failure rates may indicate misconfigured tooling or permissions issues:
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 25Work group SLA compliance — Percentage of queries completing within target thresholds per work group:
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