Adding a window frame (ROWS BETWEEN) to OVER(ORDER BY ...) controls the range of rows to aggregate. It is essential for implementing moving averages.

AVG(revenue) OVER (
  PARTITION BY company_name
  ORDER BY     quarter
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- Average of the latest 3 rows
)

Using quarterly revenue data for three companies, calculate each company's three-quarter moving average (moving_avg_3q, rounded to one decimal place). Return company_name, quarter, revenue, moving_avg_3q, sorted by company_name ascending and then quarter ascending.

Expected output (company_name ascending → quarter ascending):

At the first and second rows, the window contracts to the rows that exist (Q1 averages one row; Q2 averages two rows). AlphaTech Q4 has the highest moving average at 346.7.

SELECT
  company_name,
  quarter,
  revenue,
  ROUND(
    AVG(revenue) OVER (
      PARTITION BY company_name            -- An independent window for each company
      ORDER BY     quarter               -- Ascending quarter order defines the “current row”
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- Cover the latest 3 quarters
    ), 1
  ) AS moving_avg_3q
FROM   quarterly_revenue
ORDER BY company_name, quarter;

/*
  Execution order (logical SQL evaluation order):
  1. FROM quarterly_revenue          → Read the rows
  2. AVG(revenue) OVER (...)         → Evaluate the window function while preserving the row count
  3. SELECT                          → Evaluate the columns (moving_avg_3q)
  4. ORDER BY company_name, quarter  → Sort and return the result
*/

HAVING filters aggregate results after GROUP BY. WHERE is a row-level filter applied before aggregation, whereas HAVING is a group-level filter applied after aggregation.

SELECT   company_name, SUM(revenue)
FROM     data
WHERE    year = 2024         -- ① Row-level filter before aggregation
GROUP BY company_name
HAVING   SUM(revenue) > 1000  -- ② Group-level filter after aggregation

From quarterly performance data for three competitors, select the competitors that satisfy all three conditions: “annual total revenue of at least JPY 100 billion,” “average churn rate of at most 5.0%,” and “at least 500,000 new customers acquired during the year.” Return company_name, total_revenue, avg_churn_pct, total_new_customers, sorted by total_revenue descending.

Expected output (total_revenue descending):

BetaSoft fails because avg_churn=6.4% (>5.0). GammaSys fails because total_revenue=600 (<1000) and total_new_customers=39 (<50). Only AlphaTech passes every condition.

SELECT
  company_name,
  SUM(revenue)                    AS total_revenue,
  ROUND(AVG(churn_rate_pct), 1)  AS avg_churn_pct,
  SUM(new_customers)              AS total_new_customers
FROM   competitor_metrics
GROUP BY company_name
HAVING   SUM(revenue)          >= 1000   -- Condition ①: annual revenue of at least JPY 100 billion
     AND AVG(churn_rate_pct)   <=    5.0  -- Condition ②: average churn rate of at most 5.0%
     AND SUM(new_customers)    >=   50   -- Condition ③: at least 500,000 new customers during the year
ORDER BY total_revenue DESC;

/*
  Execution order (logical SQL evaluation order):
  1. FROM competitor_metrics      → Read the rows
  2. GROUP BY company_name        → Form the groups
  3. SUM/AVG                      → Evaluate the aggregate functions
  4. HAVING (3 conditions)        → Filter the groups
  5. SELECT                       → Evaluate the columns
  6. ORDER BY total_revenue DESC  → Sort and return the result
*/

CROSS JOIN generates every combination of rows from its left and right tables: the Cartesian product. Combined with LEFT JOIN, it is useful when you need a complete matrix with no missing combinations, such as “all companies × all categories.”

SELECT c.company_name, cat.category_name,
       COALESCE(sd.revenue, 0) AS revenue   -- Replace NULL with 0
FROM   companies   c
CROSS JOIN categories cat              -- Generate every combination (3 companies × 3 categories = 9 rows)
LEFT JOIN  sales_data sd
         ON sd.company_id  = c.company_id
        AND sd.category_id = cat.category_id  -- No recorded result produces NULL

Using the revenue records for three companies, generate an all-company × all-category matrix and fill categories with no entry with 0. Return company_name, category_name, annual_revenue, using 0 for a missing value, and sort by company_name ascending and then category_id ascending.

Expected output (company_name ascending → category_id ascending):

The three rows whose annual_revenue is 0 are unentered categories filled by COALESCE. The six recorded rows expand to nine rows: all companies × all categories.

SELECT
  c.company_name,
  cat.category_name,
  COALESCE(sd.annual_revenue, 0) AS annual_revenue  -- Replace NULL with 0
FROM       companies   c
CROSS JOIN categories  cat                     -- Every one of 3 companies × 3 categories = 9 combinations
LEFT JOIN  sales_data  sd
           ON  sd.company_id  = c.company_id
           AND sd.category_id = cat.category_id  -- Match on both keys
ORDER BY c.company_name, cat.category_id;

/*
  Execution order (logical SQL evaluation order):
  1. FROM companies c                    → Read the rows
  2. CROSS JOIN categories cat           → Join with a Cartesian product
  3. LEFT JOIN sales_data sd             → Join while preserving every left-side row
  4. SELECT                              → Evaluate the columns and format annual_revenue
  5. ORDER BY company_name, category_id  → Sort and return the result
*/

A running total is implemented by giving SUM() OVER (ORDER BY ...) the window frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Cumulative share in descending value order is the foundation of Pareto analysis.

SUM(revenue) OVER (
  ORDER BY revenue DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- Cumulative total from the first row through the current row
) AS cumulative_revenue

Using annual revenue for six IT competitors, calculate cumulative revenue (cumulative_revenue) and cumulative share (cumulative_share_pct, rounded to one decimal place) in descending revenue order. This Pareto analysis visualizes what percentage of the market is held by the top N companies. Return company_name, annual_revenue, cumulative_revenue, cumulative_share_pct, sorted by annual_revenue descending.

Expected output (annual_revenue descending):

The top two companies, AlphaTech and BetaSoft, hold a cumulative 73.7% share. The top three hold 89.5%, so the third company is where the cumulative share reaches 80%.

SELECT
  company_name,
  annual_revenue,
  SUM(annual_revenue) OVER (
    ORDER BY annual_revenue DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- Cumulative total from the first row through the current row
  ) AS cumulative_revenue,
  ROUND(
    SUM(annual_revenue) OVER (
      ORDER BY annual_revenue DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) * 100.0 /
    SUM(annual_revenue) OVER (),                      -- Denominator: grand total (OVER() = all rows)
    1
  ) AS cumulative_share_pct
FROM   competitor_revenue
ORDER BY annual_revenue DESC;

/*
  Execution order (logical SQL evaluation order):
  1. FROM competitor_revenue       → Read the rows
  2. SUM(...) OVER ()              → Attach the grand total to every row
  3. SUM(...) OVER (...)           → Calculate the running total
  4. Cumulative value / grand total      → Calculate cumulative share (%)
  5. SELECT                        → Select the columns
  6. ORDER BY annual_revenue DESC  → Sort and return the result
  */