# Redshift Workgroup Usage Analysis
## Summary
This document provides a comprehensive set of SQL queries for analyzing Amazon Redshift workgroup usage patterns and performance metrics using the SYS_QUERY_DETAIL and SYS_SERVERLESS_USAGE system tables. The analysis is divided into five main components:

1. User Usage Analysis: Tracks individual user activity with configurable usage tiers based on query duration, data processing, and resource consumption metrics.

2. Temporal Usage Patterns: Examines usage distribution across different time periods (daily and hourly) to identify peak usage times and usage patterns.

3. Query Performance Analysis: Identifies longest running queries and resource-intensive operations to highlight optimization opportunities.

4. Compute Usage Analysis: Analyzes RPU (Redshift Processing Units) consumption, storage usage, and correlates it with user activity and query patterns.

5. Performance Optimization: Provides detailed insights into step-level performance, table access patterns, and query complexity to help identify bottlenecks and optimization candidates.

Each section includes configurable thresholds and detailed metrics to support workload management and performance tuning decisions.

Reference table documentation: [SYS_QUERY_DETAIL](https://docs.aws.amazon.com/redshift/latest/dg/SYS_QUERY_DETAIL.html), [SYS_SERVERLESS_USAGE](https://docs.aws.amazon.com/redshift/latest/dg/SYS_SERVERLESS_USAGE.html).

## 1. User usage statistics
Sets configurable thresholds in the parameters CTE.

Categorizes users into usage tiers based on their total query duration.

Orders results by total duration to easily identify heavy users.

Calculates per-user statistics for the current month:
* Total number of queries
* Total and average duration
* Total data processed (in MB)
* Total rows produced
* Total blocks read
* Total blocks spilled (both local and remote)

WITH parameters AS ( -- Define usage thresholds (in minutes)
  SELECT 
    60 as low_threshold,      -- 1 hour
    180 as medium_low_threshold,  -- 3 hours
    360 as medium_threshold,      -- 6 hours
    720 as medium_high_threshold  -- 12 hours
),
user_stats AS (
  SELECT 
    user_id,
    COUNT(DISTINCT query_id) as total_queries,
    SUM(duration)/1000000.0 as total_duration_seconds,
    AVG(duration)/1000000.0 as avg_duration_seconds,
    SUM(input_bytes)/1024/1024.0 as total_mb_processed,
    SUM(output_rows) as total_rows_produced,
    SUM(blocks_read) as total_blocks_read,
    SUM(spilled_block_local_disk + spilled_block_remote_disk) as total_blocks_spilled
  FROM sys_query_detail
  GROUP BY user_id
)
SELECT 
    us.user_id,
    us.total_queries,
    ROUND(us.total_duration_seconds/60.0, 2) as total_duration_minutes,
    ROUND(us.avg_duration_seconds, 2) as avg_duration_seconds,
    ROUND(us.total_mb_processed, 2) as total_mb_processed,
    us.total_rows_produced,
    us.total_blocks_read,
    us.total_blocks_spilled,
    CASE 
        WHEN us.total_duration_seconds/60.0 <= p.low_threshold THEN 'low'
        WHEN us.total_duration_seconds/60.0 <= p.medium_low_threshold THEN 'medium low'
        WHEN us.total_duration_seconds/60.0 <= p.medium_threshold THEN 'medium'
        WHEN us.total_duration_seconds/60.0 <= p.medium_high_threshold THEN 'medium high'
        ELSE 'high'
    END as usage_tier
FROM user_stats us
CROSS JOIN parameters p
ORDER BY total_duration_seconds DESC;

## 2. Daily and hourly usage statistics
Sets configurable thresholds in the parameters CTE.
Categorizes usage into tiers based on the total amount of queries.

### 2.1 Daily usage
Calculates per-day statistics for the current month:
* Daily usage patterns with date and day of week.
* Total queries, duration, and data processed per day
* Number of distinct users per day

WITH parameters AS (
  SELECT 
    1000 as low_threshold,      -- Adjust these thresholds based on your workload
    5000 as medium_low_threshold,
    10000 as medium_threshold,
    20000 as medium_high_threshold
),
daily_stats AS (
  SELECT 
    DATE_TRUNC('day', start_time) as usage_date,
    EXTRACT(DOW FROM start_time) as day_of_week,
    COUNT(DISTINCT query_id) as total_queries,
    SUM(duration)/1000000.0 as total_duration_seconds,
    SUM(input_bytes)/1024/1024.0 as total_mb_processed,
    SUM(output_rows) as total_rows_produced,
    SUM(blocks_read) as total_blocks_read,
    COUNT(DISTINCT user_id) as distinct_users
  FROM sys_query_detail
  GROUP BY DATE_TRUNC('day', start_time), EXTRACT(DOW FROM start_time)
)
SELECT 
    usage_date,
    CASE day_of_week
        WHEN 0 THEN 'Sunday'
        WHEN 1 THEN 'Monday'
        WHEN 2 THEN 'Tuesday'
        WHEN 3 THEN 'Wednesday'
        WHEN 4 THEN 'Thursday'
        WHEN 5 THEN 'Friday'
        WHEN 6 THEN 'Saturday'
    END as day_name,
    total_queries,
    ROUND(total_duration_seconds/60.0, 2) as total_duration_minutes,
    ROUND(total_mb_processed, 2) as total_mb_processed,
    total_rows_produced,
    distinct_users,
    CASE 
        WHEN total_queries <= p.low_threshold THEN 'low'
        WHEN total_queries <= p.medium_low_threshold THEN 'medium low'
        WHEN total_queries <= p.medium_threshold THEN 'medium'
        WHEN total_queries <= p.medium_high_threshold THEN 'medium high'
        ELSE 'high'
    END as usage_tier
FROM daily_stats
CROSS JOIN parameters p
ORDER BY usage_date;

### 2.2 Hourly usage

Calculates per-hour statistics for the current month:
* Hour-by-hour usage patterns
* Query volume distribution for each hour
* Number of distinct users per hour

WITH hourly_stats AS (
  SELECT 
    EXTRACT(HOUR FROM start_time) as hour_of_day,
    COUNT(DISTINCT query_id) as total_queries,
    SUM(duration)/1000000.0 as total_duration_seconds,
    SUM(input_bytes)/1024/1024.0 as total_mb_processed,
    SUM(output_rows) as total_rows_produced,
    COUNT(DISTINCT user_id) as distinct_users,
    AVG(duration)/1000000.0 as avg_query_duration_seconds
  FROM sys_query_detail
  GROUP BY EXTRACT(HOUR FROM start_time)
)
SELECT 
    hour_of_day,
    LPAD(hour_of_day::text || ':00', 5, '0') as hour_range,
    total_queries,
    ROUND(total_duration_seconds/60.0, 2) as total_duration_minutes,
    ROUND(total_mb_processed, 2) as total_mb_processed,
    total_rows_produced,
    distinct_users,
    ROUND(avg_query_duration_seconds, 2) as avg_query_duration_seconds
FROM hourly_stats
ORDER BY hour_of_day;

## 3. Query statistics

Sets configurable thresholds in the parameters CTE.
Categorizes query consumption into tiers based on the duration of queries and resource usage during the month.

### 3.1 Longest-running queries
Calculates the longest-running queries based on the thresholds.
* Duration time in seconds and minutes
* User ID, and start and end time
* Data processing metrics

WITH parameters AS (
  SELECT 
    60 as low_duration_threshold,      -- 1 minute
    300 as medium_low_duration_threshold,  -- 5 minutes
    900 as medium_duration_threshold,      -- 15 minutes
    1800 as medium_high_duration_threshold -- 30 minutes
)
SELECT 
    query_id,
    ROUND(duration/1000000.0, 2) as duration_seconds,
    ROUND(duration/1000000.0/60, 2) as duration_minutes,
    user_id,
    start_time,
    end_time,
    ROUND(input_bytes/1024/1024.0, 2) as input_mb,
    output_rows,
    CASE 
        WHEN duration/1000000.0 <= p.low_duration_threshold THEN 'low'
        WHEN duration/1000000.0 <= p.medium_low_duration_threshold THEN 'medium low'
        WHEN duration/1000000.0 <= p.medium_duration_threshold THEN 'medium'
        WHEN duration/1000000.0 <= p.medium_high_duration_threshold THEN 'medium high'
        ELSE 'high'
    END as duration_tier
FROM sys_query_detail
CROSS JOIN parameters p
ORDER BY duration DESC
LIMIT 100;

### 3.2 Data consumption statistics
Identifies data-intensive queries
* Detailed resource metrics (IO, spill, skewness)
* Processing volume metrics
* Performance indicators like data and time skewness

WITH parameters AS (
  SELECT 
    1000 as low_mb_threshold,      -- Adjust MB processed thresholds
    5000 as medium_low_mb_threshold,
    10000 as medium_mb_threshold,
    50000 as medium_high_mb_threshold
),
query_resources AS (
  SELECT 
    query_id,
    COUNT(*) as execution_count,
    SUM(duration)/1000000.0 as total_duration_seconds,
    AVG(duration)/1000000.0 as avg_duration_seconds,
    SUM(input_bytes)/1024/1024.0 as total_mb_processed,
    SUM(output_rows) as total_rows_produced,
    SUM(blocks_read) as total_blocks_read,
    SUM(local_read_IO) as total_local_io,
    SUM(remote_read_IO) as total_remote_io,
    SUM(spilled_block_local_disk) as local_spill,
    SUM(spilled_block_remote_disk) as remote_spill,
    MAX(data_skewness) as max_data_skewness,
    MAX(time_skewness) as max_time_skewness
  FROM sys_query_detail
  GROUP BY query_id
)
SELECT 
    query_id,
    execution_count,
    ROUND(total_duration_seconds/60.0, 2) as total_duration_minutes,
    ROUND(avg_duration_seconds, 2) as avg_duration_seconds,
    ROUND(total_mb_processed, 2) as total_mb_processed,
    total_rows_produced,
    total_blocks_read,
    total_local_io,
    total_remote_io,
    local_spill + remote_spill as total_spill,
    max_data_skewness,
    max_time_skewness,
    CASE 
        WHEN total_mb_processed <= p.low_mb_threshold THEN 'low'
        WHEN total_mb_processed <= p.medium_low_mb_threshold THEN 'medium low'
        WHEN total_mb_processed <= p.medium_mb_threshold THEN 'medium'
        WHEN total_mb_processed <= p.medium_high_mb_threshold THEN 'medium high'
        ELSE 'high'
    END as resource_consumption_tier
FROM query_resources
CROSS JOIN parameters p
ORDER BY total_mb_processed DESC
LIMIT 100;

## 4 Compute usage analysis
Identify peak compute usage and correlate it with users and query usage statistics.
Includes charged hours from billing metrics, average storage used. Distincs users per hour.
### 4.1 RPU and storage usage statistics

WITH hourly_compute AS (
  SELECT 
    DATE_TRUNC('hour', start_time) as usage_hour,
    AVG(compute_capacity) as avg_rpus,
    MAX(compute_capacity) as peak_rpus,
    SUM(compute_seconds)/3600.0 as compute_hours,
    SUM(charged_seconds)/3600.0 as charged_hours,
    AVG(data_storage)/1024.0 as avg_storage_gb
  FROM sys_serverless_usage
  GROUP BY DATE_TRUNC('hour', start_time)
),
user_query_stats AS (
  SELECT 
    DATE_TRUNC('hour', start_time) as usage_hour,
    user_id,
    COUNT(DISTINCT query_id) as user_queries,
    SUM(duration)/1000000.0 as user_duration_seconds,
    SUM(input_bytes)/1024/1024.0 as user_mb_processed,
    SUM(output_rows) as user_rows_produced,
    SUM(blocks_read) as user_blocks_read,
    SUM(spilled_block_local_disk + spilled_block_remote_disk) as user_blocks_spilled
  FROM sys_query_detail
  GROUP BY DATE_TRUNC('hour', start_time), user_id
),
hourly_stats AS (
  SELECT 
    usage_hour,
    COUNT(DISTINCT user_id) as distinct_users,
    SUM(user_queries) as total_queries,
    AVG(user_queries) as avg_queries_per_user,
    MAX(user_queries) as max_queries_per_user,
    SUM(user_duration_seconds) as total_duration_seconds,
    AVG(user_duration_seconds) as avg_duration_per_user,
    SUM(user_mb_processed) as total_mb_processed,
    SUM(user_rows_produced) as total_rows_produced,
    SUM(user_blocks_read) as total_blocks_read,
    SUM(user_blocks_spilled) as total_blocks_spilled
  FROM user_query_stats
  GROUP BY usage_hour
)
SELECT 
    hc.usage_hour,
    EXTRACT(DOW FROM hc.usage_hour) as day_of_week,
    EXTRACT(HOUR FROM hc.usage_hour) as hour_of_day,
    -- Compute capacity metrics
    ROUND(hc.avg_rpus, 2) as avg_rpus,
    ROUND(hc.peak_rpus, 2) as peak_rpus,
    ROUND(hc.compute_hours, 2) as compute_hours,
    ROUND(hc.charged_hours, 2) as charged_hours,
    ROUND(hc.avg_storage_gb, 2) as avg_storage_gb,
    -- User activity metrics
    hs.distinct_users,
    hs.total_queries,
    ROUND(hs.avg_queries_per_user, 1) as avg_queries_per_user,
    hs.max_queries_per_user,
    -- Workload metrics
    ROUND(hs.total_duration_seconds/60.0, 2) as total_duration_minutes,
    ROUND(hs.avg_duration_per_user/60.0, 2) as avg_minutes_per_user,
    ROUND(hs.total_mb_processed, 2) as total_mb_processed,
    hs.total_rows_produced,
    hs.total_blocks_read,
    hs.total_blocks_spilled
FROM hourly_compute hc
LEFT JOIN hourly_stats hs ON hc.usage_hour = hs.usage_hour
ORDER BY hc.peak_rpus DESC, hc.avg_rpus DESC;

## 5. Performance improvement analysis
Identify possible bottlenecks, possible queries optimization candidates, and table optimization
### 5.1 Step-level performance statistics
Step-level performance metrics to identify bottlenecks in query execution

WITH step_stats AS (
  SELECT 
    step_name,
    COUNT(*) as occurrence_count,
    AVG(duration)/1000000.0 as avg_duration_seconds,
    SUM(input_bytes)/1024/1024.0 as total_mb_processed,
    AVG(data_skewness) as avg_data_skewness,
    AVG(time_skewness) as avg_time_skewness
  FROM sys_query_detail
  GROUP BY step_name
)
SELECT 
    step_name,
    occurrence_count,
    ROUND(avg_duration_seconds, 2) as avg_duration_seconds,
    ROUND(total_mb_processed, 2) as total_mb_processed,
    ROUND(avg_data_skewness, 2) as avg_data_skewness,
    ROUND(avg_time_skewness, 2) as avg_time_skewness
FROM step_stats
ORDER BY avg_duration_seconds DESC;

### 5.2 Table access patterns statistics
Table access patterns to optimize frequently accessed tables

SELECT 
    table_name,
    COUNT(DISTINCT query_id) as query_count,
    COUNT(DISTINCT user_id) as user_count,
    SUM(input_bytes)/1024/1024.0 as total_mb_read,
    SUM(blocks_read) as total_blocks_read,
    AVG(duration)/1000000.0 as avg_step_duration_seconds,
    MAX(is_rrscan) as uses_range_restricted_scan
FROM sys_query_detail
GROUP BY table_name
ORDER BY query_count DESC;

### 5.3 Query complexity analysis
Query complexity metrics to identify candidates for optimization

WITH parameters AS (
    SELECT 
        50 as simple_threshold,      -- Queries with 5 or fewer steps
        100 as moderate_threshold,   -- Queries with 6-15 steps
        200 as complex_threshold     -- Queries with 16-30 steps
                                    -- More than 30 steps = Very Complex
),
query_steps AS (
    SELECT 
        query_id,
        COUNT(CASE WHEN step_id >= 0 THEN 1 END) as total_steps,
        COUNT(DISTINCT step_name) as distinct_step_types,
        SUM(duration)/1000000.0 as total_duration_seconds,
        SUM(input_bytes)/1024/1024.0 as total_mb_processed,
        SUM(output_rows) as total_rows_produced,
        MAX(data_skewness) as max_data_skewness,
        MAX(time_skewness) as max_time_skewness
    FROM sys_query_detail
    GROUP BY query_id
)
SELECT 
    query_id,
    total_steps,
    distinct_step_types,
    ROUND(total_duration_seconds, 2) as duration_seconds,
    ROUND(total_mb_processed, 2) as mb_processed,
    total_rows_produced,
    ROUND(max_data_skewness, 2) as max_data_skewness,
    ROUND(max_time_skewness, 2) as max_time_skewness,
    CASE 
        WHEN total_steps <= p.simple_threshold THEN 'Simple'
        WHEN total_steps <= p.moderate_threshold THEN 'Moderate'
        WHEN total_steps <= p.complex_threshold THEN 'Complex'
        ELSE 'Very Complex'
    END as complexity_category
FROM query_steps
CROSS JOIN parameters p
ORDER BY total_steps DESC;

## 6. Workload categorization query

WITH scan_sum AS
(
  SELECT query_id,
         segment_id,
         SUM(output_bytes) AS bytes,
         ROUND(datediff (ms,MIN(start_time),MAX(end_time)) / 1000.0,3) AS seg_sec
  FROM sys_query_detail
  WHERE user_id > 1
  AND step_name='scan'
  AND   start_time > '2000-01-01 00:00:00'
  AND   end_time > '2000-01-01 00:00:00'
  GROUP BY query_id,
           segment_id
),
scan_list AS
(
  SELECT query_id,
         MAX(bytes) AS max_scan_bytes,
         MAX(CASE WHEN seg_sec > 0 THEN seg_sec ELSE 0 END) AS seg_sec_max
  FROM scan_sum
  GROUP BY query_id
) ,
query_list AS
(
  SELECT w.query_id,
         start_time,
         end_time,
         ROUND(execution_time / 1000 / 1000.0,3) AS exec_sec,
         max_scan_bytes,
         seg_sec_max,
         CASE
           WHEN max_scan_bytes <     1000000000 THEN 'xx-small-8RPU'
           WHEN max_scan_bytes <    10000000000 THEN 'x-small-32RPU'
           WHEN max_scan_bytes <   100000000000 THEN 'small-64RPU'
           WHEN max_scan_bytes <   500000000000 THEN 'medium-128RPU'
           WHEN max_scan_bytes <  1000000000000 THEN 'large-256RPU'
           WHEN max_scan_bytes <  3000000000000 THEN 'x-large-512RPU'
           WHEN max_scan_bytes >  3000000000000 THEN 'xx-large-1024RPU'
           else 'N/A'
         END AS size_type
  FROM sys_query_history w,
       scan_list sc
  WHERE sc.query_id = w.query_id
)
SELECT size_type,
       COUNT(*) AS query_cnt,
       AVG(exec_sec) AS exec_sec_avg,
       MAX(exec_sec) AS exec_sec_max,
       MIN(exec_sec) AS exec_sec_min,
       AVG(seg_sec_max) AS seg_sec_max_avg,
       AVG(max_scan_bytes) AS max_scan_bytes_avg,
       COUNT(*) * AVG(exec_sec) AS weightage
FROM query_list
GROUP BY 1
ORDER BY max_scan_bytes_avg
;

## 7. Cost estimation query

WITH recursive numbers(rown) as (
    select 1
    UNION ALL
    select rown + 1 from numbers where rown < 10080
),
epochval as (
    select extract(epoch from date_trunc('day',current_timestamp)) - rown*60 ep from numbers
),
t_minute as (
    select TIMESTAMP 'epoch' + (cast(ep as bigint)) *INTERVAL '1 second' minutes_week 
    from epochval order by minutes_week
),
scan_sum AS (
    SELECT query_id,
           segment_id,
           SUM(output_bytes) AS bytes,
           ROUND(datediff (ms,MIN(start_time),MAX(end_time)) / 1000.0,3) AS seg_sec
    FROM sys_query_detail
    WHERE user_id > 1
    AND step_name='scan'
    AND start_time > '2000-01-01 00:00:00'
    AND end_time > '2000-01-01 00:00:00'
    GROUP BY query_id, segment_id
),
scan_list AS (
    SELECT query_id,
           MAX(bytes) AS max_scan_bytes,
           MAX(CASE WHEN seg_sec > 0 THEN seg_sec ELSE 0 END) AS seg_sec_max
    FROM scan_sum
    GROUP BY query_id
),
query_list AS (
    SELECT max_scan_bytes,
           ROUND(execution_time / 1000 / 1000.0,3) AS exec_sec,
           CASE
               WHEN max_scan_bytes < 1000000000 THEN '8'
               WHEN max_scan_bytes < 10000000000 THEN '32'
               WHEN max_scan_bytes < 100000000000 THEN '64'
               WHEN max_scan_bytes < 500000000000 THEN '128'
               WHEN max_scan_bytes < 1000000000000 THEN '256'
               WHEN max_scan_bytes < 3000000000000 THEN '512'
               WHEN max_scan_bytes > 3000000000000 THEN '1024'
               else 'N/A'
           END AS recommended_RPU_by_workload
    FROM sys_query_history w,
         scan_list sc
    WHERE sc.query_id = w.query_id
),
workload_weightage AS (
    SELECT recommended_RPU_by_workload,
           COUNT(*) * AVG(exec_sec) weightage
    FROM query_list
    GROUP BY 1
),
dominant_workload AS (
    SELECT recommended_RPU_by_workload
    FROM workload_weightage
    where weightage = (select max(weightage) from workload_weightage)
)

SELECT distinct trunc(minutes_week) data_day,
       (select recommended_RPU_by_workload from dominant_workload) as recommended_base_RPU,
       ceiling(1.0*count(distinct minutes_week)) As estimated_serverless_minutes_per_day,
       concat(round(100.0*count(distinct minutes_week)/1440,2),'%') As estimated_serverless_usage_percentage,
       round(0.375*24*(select recommended_RPU_by_workload from dominant_workload)*count(distinct minutes_week)/1440,2) As estimated_serverless_daily_cost
FROM t_minute
GROUP BY trunc(minutes_week)
ORDER BY 1;