CTEs, Window Functions, and Subqueries

CTEs: Make Complex Queries Readable

A Common Table Expression (CTE) is a named temporary result set defined with WITH. It's like a helper query that you name, then use in your main query. CTEs are invaluable for ICM reporting because they break complex multi-step logic into readable chunks.

Here's a real ICM problem: "Show me quota attainment (actual earnings ÷ quota × 100%) for each participant in Q1 2026, ranked by attainment." To solve this, you need:

• Quota amounts from the quota table (step 1)
• Actual results from the results table (step 2)
• Join them and calculate attainment (step 3)
• Rank the results (step 4)

Without CTEs, this becomes a nested mess. With CTEs, it's clear:

WITH
-- Step 1: Get quota targets
quota_base AS (
  SELECT
    PARTICIPANT_ID,
    SUM(QUOTA_AMOUNT) AS TOTAL_QUOTA
  FROM CSC_QUOTA
  WHERE PERIOD_ID = '2026-Q1'
  GROUP BY PARTICIPANT_ID
),
-- Step 2: Get actual results
results_base AS (
  SELECT
    PARTICIPANT_ID,
    SUM(RESULT_AMOUNT) AS TOTAL_EARNED
  FROM CSC_RESULTS
  WHERE PERIOD_ID = '2026-Q1'
  GROUP BY PARTICIPANT_ID
),
-- Step 3: Calculate attainment
attainment_calc AS (
  SELECT
    p.PARTICIPANT_ID,
    p.NAME,
    q.TOTAL_QUOTA,
    r.TOTAL_EARNED,
    CASE
      WHEN q.TOTAL_QUOTA = 0 THEN 0
      ELSE ROUND(r.TOTAL_EARNED / q.TOTAL_QUOTA * 100, 2)
    END AS ATTAINMENT_PCT
  FROM     CSC_PARTICIPANT p
  LEFT JOIN quota_base q
    ON q.PARTICIPANT_ID = p.PARTICIPANT_ID
  LEFT JOIN results_base r
    ON r.PARTICIPANT_ID = p.PARTICIPANT_ID
)
-- Step 4: Main query using the CTEs
SELECT * FROM attainment_calc
ORDER BY ATTAINMENT_PCT DESC;

Each CTE is defined once and can be referenced multiple times. The WITH clause combines all CTEs (separated by commas), then your main SELECT uses them. This is production-quality code: readable, maintainable, debuggable.

Window Functions: Analytics Without GROUP BY

Window functions perform calculations across a "window" of rows related to the current row, without collapsing rows like GROUP BY does.

Common window functions:

• ROW_NUMBER() — Assigns a unique number to each row within the window
• RANK() — Ranks rows, with ties getting the same rank (1, 1, 3)
• DENSE_RANK() — Ranks rows, densely (1, 1, 2)
• SUM() OVER (...) — Running total or window sum
• LAG() / LEAD() — Previous/next row's value (for period-over-period comparisons)

The syntax is: FUNCTION() OVER (PARTITION BY col ORDER BY col ROWS BETWEEN ...)

Ranking Participants by Earnings

"Rank participants by total earnings in Q1 2026, highest to lowest."

SELECT
  p.PARTICIPANT_ID,
  p.NAME,
  r.RESULT_AMOUNT,
  RANK() OVER (
    ORDER BY r.RESULT_AMOUNT DESC
  ) AS EARNINGS_RANK
FROM     CSC_PARTICIPANT p
LEFT JOIN CSC_RESULTS r
  ON r.PARTICIPANT_ID = p.PARTICIPANT_ID
WHERE    r.PERIOD_ID = '2026-Q1'
ORDER BY EARNINGS_RANK;

Each row gets a rank based on RESULT_AMOUNT descending. If two participants have the same earnings, they get the same rank (1, 1, 3). No GROUP BY — each result row is preserved.

Running Totals with SUM() OVER

"Show each result transaction with a running total of earnings for that participant across all periods."

SELECT
  p.PARTICIPANT_ID,
  p.NAME,
  r.PERIOD_ID,
  r.RESULT_AMOUNT,
  SUM(r.RESULT_AMOUNT) OVER (
    PARTITION BY p.PARTICIPANT_ID
    ORDER BY r.PERIOD_ID
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS RUNNING_TOTAL
FROM     CSC_PARTICIPANT p
LEFT JOIN CSC_RESULTS r
  ON r.PARTICIPANT_ID = p.PARTICIPANT_ID
ORDER BY p.PARTICIPANT_ID, r.PERIOD_ID;

Breaking down the OVER clause:

• PARTITION BY p.PARTICIPANT_ID — Separate windows for each participant
• ORDER BY r.PERIOD_ID — Within each window, order by period
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — Sum from the first row to the current row (classic running total)

The result: for each participant, each row shows that period's earning plus a cumulative sum up to that point.

Partition By for Per-Period Rankings

"Rank participants within each period (top earner per period = rank 1)."

SELECT
  r.PERIOD_ID,
  p.PARTICIPANT_ID,
  p.NAME,
  r.RESULT_AMOUNT,
  RANK() OVER (
    PARTITION BY r.PERIOD_ID
    ORDER BY r.RESULT_AMOUNT DESC
  ) AS PERIOD_RANK
FROM     CSC_PARTICIPANT p
LEFT JOIN CSC_RESULTS r
  ON r.PARTICIPANT_ID = p.PARTICIPANT_ID
ORDER BY r.PERIOD_ID, PERIOD_RANK;

With PARTITION BY, ranking resets for each period. Q1 has its own rank 1 (highest Q1 earner), Q2 has its own rank 1 (highest Q2 earner).

Subqueries: Embedding Queries

A subquery is a query inside another query. Subqueries can appear in SELECT, FROM, or WHERE clauses.

Subquery in WHERE

"Find participants whose earnings are above the average."

SELECT p.PARTICIPANT_ID, p.NAME, r.RESULT_AMOUNT
FROM     CSC_PARTICIPANT p
LEFT JOIN CSC_RESULTS r
  ON r.PARTICIPANT_ID = p.PARTICIPANT_ID
WHERE    r.RESULT_AMOUNT > (
  SELECT AVG(RESULT_AMOUNT) FROM CSC_RESULTS
  WHERE PERIOD_ID = '2026-Q1'
);

The subquery in parentheses calculates the average, and the outer query filters to rows above that average.

Subquery in FROM

Treat a subquery result as a table:

SELECT
  participant_earnings.PARTICIPANT_ID,
  participant_earnings.NAME,
  participant_earnings.TOTAL_EARNED
FROM (
  SELECT
    p.PARTICIPANT_ID,
    p.NAME,
    SUM(r.RESULT_AMOUNT) AS TOTAL_EARNED
  FROM     CSC_PARTICIPANT p
  LEFT JOIN CSC_RESULTS r
    ON r.PARTICIPANT_ID = p.PARTICIPANT_ID
  WHERE    r.PERIOD_ID = '2026-Q1'
  GROUP BY p.PARTICIPANT_ID, p.NAME
) participant_earnings
WHERE participant_earnings.TOTAL_EARNED > 10000;

The subquery (aliased as participant_earnings) produces a result set, then the outer query filters it. This is often more readable as a CTE.

CTEs vs Subqueries: Choose CTEs

Both CTEs and subqueries solve the same problem: breaking logic into steps. CTEs are preferred because they're more readable:

Write subqueries only when you have a simple, single-use calculation. For anything complex, use a CTE.

Complex Real-World Example: Quota Attainment with Rankings

"Show quota attainment for Q1 2026, ranked by attainment within each region, with the regional average attainment."

WITH
quota_data AS (
  SELECT PARTICIPANT_ID, SUM(QUOTA_AMOUNT) AS TOTAL_QUOTA
  FROM CSC_QUOTA
  WHERE PERIOD_ID = '2026-Q1'
  GROUP BY PARTICIPANT_ID
),
results_data AS (
  SELECT PARTICIPANT_ID, SUM(RESULT_AMOUNT) AS TOTAL_EARNED
  FROM CSC_RESULTS
  WHERE PERIOD_ID = '2026-Q1'
  GROUP BY PARTICIPANT_ID
),
attainment AS (
  SELECT
    p.PARTICIPANT_ID,
    p.NAME,
    p.REGION,
    COALESCE(q.TOTAL_QUOTA, 0) AS QUOTA,
    COALESCE(r.TOTAL_EARNED, 0) AS EARNED,
    CASE
      WHEN COALESCE(q.TOTAL_QUOTA, 0) = 0 THEN 0
      ELSE ROUND(r.TOTAL_EARNED / q.TOTAL_QUOTA * 100, 2)
    END AS ATTAINMENT_PCT
  FROM     CSC_PARTICIPANT p
  LEFT JOIN quota_data q ON q.PARTICIPANT_ID = p.PARTICIPANT_ID
  LEFT JOIN results_data r ON r.PARTICIPANT_ID = p.PARTICIPANT_ID
  WHERE    p.STATUS = 'ACTIVE'
)
SELECT
  PARTICIPANT_ID,
  NAME,
  REGION,
  ATTAINMENT_PCT,
  RANK() OVER (
    PARTITION BY REGION
    ORDER BY ATTAINMENT_PCT DESC
  ) AS REGION_RANK,
  AVG(ATTAINMENT_PCT) OVER (
    PARTITION BY REGION
  ) AS REGION_AVG
FROM attainment
ORDER BY REGION, REGION_RANK;

This query:

• Uses CTEs to break data into quota and results
• Calculates attainment in a third CTE
• Uses window functions to rank within regions and calculate regional averages
• Preserves all rows (no GROUP BY collapsing)
• Is production-ready and maintainable

Summary: When to Use Each Technique

• SELECT + WHERE: Simple filtering on one table
• JOIN: Combine data from two or more tables
• GROUP BY: Collapse rows into summaries (totals, averages)
• CTE: Break complex logic into readable, reusable steps
• Window Function: Rank, count, or sum across row windows without collapsing
• Subquery: Embed a simple one-off query (avoid if you can use a CTE instead)

Resources and Next Steps

You now have the SQL foundation for ICM work. To deepen your expertise:

• Experiment: Run these queries on your own SAP SuccessFactors IM or Callidus Commissions environments. Modify them, break them, fix them.
• Understand edge cases: What happens with NULL quotas? Zero earnings? Inactive participants? Test these scenarios.
• Learn your database: SAP HANA Database and Oracle Database have different performance characteristics. Learn EXPLAIN PLAN to optimize queries.
• Join a community: Stack Overflow and database-specific forums are invaluable when you're stuck.