SQL Data Transformation — Applied LPAD/CONCAT

QUESTION 6

LPAD / RPAD / CONCAT_WS — Generate Report Codes with Zero Padding and Fixed-Width Formatting

LPAD/RPADCONCAT_WSReport Code GenerationNULL-Safe Concatenation

Background ▼

These functions pad strings to a fixed length and concatenate strings safely when NULL values are present.

LPAD('5', 6, '0')                        -- → '000005'    Pad on the left with '0' to six characters
LPAD('ABCDEFGH', 6, '0')                -- → 'ABCDEF'    Truncate the right side when input exceeds the length
RPAD('FOOD', 8, '.')                     -- → 'FOOD....'   Pad on the right with '.' to eight characters
UPPER('tokyo')                            -- → 'TOKYO'      Convert all letters to uppercase
CONCAT_WS('-', '2024', 'TKY', '000005')  -- → '2024-TKY-000005'  Join with a separator
CONCAT_WS('-', '2024', NULL, '000005')   -- → '2024-000005'      Skip NULL ✓
CONCAT('2024', '-', NULL, '-', '000005')   -- → '2024--000005'     Ignore NULL but duplicate separators ✗

The advantage of CONCAT_WS: CONCAT_WS(separator, a, b, c) automatically skips NULL arguments and inserts the separator only between the remaining values. PostgreSQL's regular CONCAT also ignores NULL, but passing separators as separate arguments can produce duplicates such as '2024--000005'. Real-world data often contains NULL values, so prefer CONCAT_WS for separator-delimited concatenation.

Problem ▼

Generate the following columns from the invoices table.
① padded_seq: a six-digit, zero-padded sequence code from the integer seq_no
② invoice_no: a report number in the form {fiscal_year}-{UPPER(branch_code)}-{padded_seq}
③ display_label: customer_name padded on the right with dots to 16 characters.

Table

▸ invoices

invoice_id	fiscal_year	branch_code	seq_no	customer_name
1	2024	tky	5	Alpine Cafe
2	2024	osk	123	Tech Corp
3	2024	tky	42	Garden Shop
4	2025	ngy	1	Book World

Expected Output ▼

invoice_id	padded_seq	invoice_no	display_label
1	000005	2024-TKY-000005	Alpine Cafe.....
2	000123	2024-OSK-000123	Tech Corp.......
3	000042	2024-TKY-000042	Garden Shop.....
4	000001	2025-NGY-000001	Book World......

Model Answer ▼

SELECT
  invoice_id,
  LPAD(seq_no::TEXT, 6, '0')          AS padded_seq,   -- Cast INTEGER to TEXT, then pad left with '0' to six digits
  CONCAT_WS(
    '-',                               -- Separator (never duplicated when a value is NULL)
    fiscal_year::TEXT,                -- Cast INTEGER to TEXT (:: is PostgreSQL-specific syntax)
    UPPER(branch_code),                -- Normalize lowercase to uppercase ('tky' → 'TKY')
    LPAD(seq_no::TEXT, 6, '0')        -- Reuse the LPAD expression in the report number
  )                                    AS invoice_no,  -- Report number in 'YYYY-XXX-NNNNNN' format
  RPAD(customer_name, 16, '.')       AS display_label  -- Fixed 16-character label padded with dots on the right
FROM   invoices
ORDER BY invoice_id;

/*
  Execution order (logical SQL evaluation order):
  1. FROM invoices                 → Read all four rows
  2. seq_no::TEXT                  → Cast INTEGER to TEXT
  3. LPAD(seq_no::TEXT, 6, '0')    → Pad left with '0' to six characters
  4. UPPER(branch_code)            → Convert every letter to uppercase
  5. CONCAT_WS('-', ...)           → Join values with '-', skipping NULL arguments
  6. RPAD(customer_name, 16, '.')  → Pad right with '.' to 16 characters
  7. ORDER BY invoice_id           → Sort ascending and return the result
  */

Explanation (table transitions & key points) ▼

SELECT
  invoice_id,
  LPAD(seq_no::TEXT, 6, '0')     AS padded_seq,
  CONCAT_WS(
    '-',
    fiscal_year::TEXT,
    UPPER(branch_code),
    LPAD(seq_no::TEXT, 6, '0')
  ) AS invoice_no,
  RPAD(customer_name, 16, '.') AS display_label
FROM   invoices
ORDER BY invoice_id;

LEGEND ▼

Rows read / loaded

① FROM — Source Data ▼

FROM invoicesbranch_code is stored in lowercase, and seq_no is an integer sequence. LPAD adds zero padding, UPPER normalizes the case, CONCAT_WS assembles the report number, and RPAD creates a fixed-width label.

1 / 5

invoice_id	fiscal_year	branch_code	seq_no	customer_name
1	2024	tky	5	Alpine Cafe
2	2024	osk	123	Tech Corp
3	2024	tky	42	Garden Shop
4	2025	ngy	1	Book World

4 rows

SELECT invoice_id,
  CONCAT(fiscal_year,'-',branch_code,'-',seq_no)
    AS concat_result,
  CONCAT_WS('-', fiscal_year::TEXT,
    UPPER(branch_code), seq_no::TEXT)
    AS concat_ws_result
FROM invoices;

LEGEND ▼

Rows read / loaded

① Sample Data Including a NULL branch_code ▼

invoices containing NULLSuppose one invoice has no branch_code and therefore contains NULL. Compare the behavior of CONCAT and CONCAT_WS.

1 / 3

invoice_id	fiscal_year	branch_code	seq_no
1	2024	'tky'	5
2	2024	NULL	123

2 rows (one contains NULL)

LEARNING POINTS

LPAD/RPAD measure characters: PostgreSQL's LPAD/RPAD use the number of characters. A multibyte character counts as one, although wide characters can occupy twice the display width in a monospace font and make visual alignment uneven. Combine these functions with presentation-layer formatting when exact display width matters.

The :: cast is PostgreSQL-specific: seq_no::TEXT is equivalent to CAST(seq_no AS TEXT). A number must be cast before it is passed to string functions such as LPAD or CONCAT_WS. Standard SQL uses CAST, while PostgreSQL's concise :: syntax is common.

Combining CONCAT_WS with COALESCE: To replace NULL with a default instead of merely skipping it, use CONCAT_WS('-', a, COALESCE(b, 'UNKNOWN'), c). COALESCE(b, 'UNKNOWN') returns 'UNKNOWN' when b is NULL.

ANTI-PATTERNS

Passing separators separately to CONCAT when values may be NULL: If branch is NULL in CONCAT(year, '-', branch, '-', seq), PostgreSQL ignores the NULL itself but duplicates the separator. Prefer CONCAT_WS for separator-delimited strings, and combine it with COALESCE(branch, 'UNKNOWN') when a missing value should be explicit.

Scattering report-number logic across SQL and application code: Inconsistencies arise when number-generation rules exist in SQL, application code, and batch jobs. Decide during design whether to centralize the logic in the database with a VIEW or GENERATED COLUMN, or in a single application function.

FIELD NOTE: Automating Zero Padding with a GENERATED COLUMN

In PostgreSQL 12 and later, GENERATED ALWAYS AS (LPAD(seq_no::TEXT, 6, '0')) STORED can calculate and store a zero-padded column automatically on INSERT. This can be more efficient than recalculating LPAD on every SELECT and prevents bugs caused by forgetting to populate the value. If the padding width may change, however, the column definition must also change. A common alternative is to manage volatile formatting in a VIEW while retaining the raw integer in storage.

QUESTION 7

AGE / INTERVAL / NOW() — Calculate Expiration, Days Remaining, and Renewal Dates in Real Time

AGE/INTERVALNOW()Date ArithmeticExpiration Management

Background ▼

These functions calculate intervals relative to the current date and time.

DATE '2024-12-15'                              -- Reproducible fixed reference date (DATE)
NOW()                                            -- → '2024-12-15 09:30+09'  Current date and time (TIMESTAMP WITH TIME ZONE)

-- Subtract DATE values → INTEGER (number of days)
'2025-01-14'::DATE - '2024-12-15'::DATE       -- → 30  (integer total days)

-- AGE → INTERVAL (human-readable relative representation)
AGE('2025-11-30'::DATE, '2024-12-15'::DATE)  -- → '11 mons 15 days' (not a total of 350 days!)
EXTRACT(DAY FROM AGE(...))                    -- → 15  ← day component only, not total days

-- Add or subtract INTERVAL to calculate future or past dates
'2025-01-14'::DATE + INTERVAL '1 year'       -- → '2026-01-14 00:00:00'
NOW() - INTERVAL '30 days'                      -- → timestamp 30 days ago

Pitfall — EXTRACT(DAY FROM AGE(...)) is not total days: AGE('2025-11-30', '2024-12-15') returns the INTERVAL '11 mons 15 days'. Applying EXTRACT(DAY FROM ...) returns 15, the day component only, not the total of 350 days. Use expires_at::DATE - DATE '2024-12-15' when you need total days as an integer.

Problem ▼

Using DATE '2024-12-15' as the reference, calculate the following from the subscriptions table (the example output uses 2024-12-15).
① days_remaining: days until expiration, negative if already expired
② status: one of 'Expired', 'Expiring Soon' (within 30 days), or 'Active'
③ renewal_at: the next scheduled renewal date (current expiration date + one year) in 'YYYY-MM-DD' format.
Sort the result by days_remaining ascending.

Table

▸ subscriptions

user_id	plan	started_at	expires_at
1	basic	2024-01-15	2025-01-14
2	pro	2023-06-01	2024-07-31
3	premium	2024-11-01	2025-11-30
4	basic	2024-03-01	2024-12-31

Expected Output ▼

user_id	plan	days_remaining	status	renewal_at
2	pro	-137	Expired	2025-07-31
4	basic	16	Expiring Soon	2025-12-31
1	basic	30	Expiring Soon	2026-01-14
3	premium	350	Active	2026-11-30

Model Answer ▼

SELECT
  user_id,
  plan,
  expires_at::DATE - DATE '2024-12-15'       AS days_remaining,      -- Difference from the exercise's fixed reference date
  CASE                                                                   -- Classify expiration status into three levels
    WHEN expires_at::DATE < DATE '2024-12-15'       THEN 'Expired'
    WHEN (expires_at::DATE - DATE '2024-12-15') <= 30 THEN 'Expiring Soon'
    ELSE                                                    'Active'
  END                                              AS status,
  TO_CHAR(                                                               -- Next renewal = current expiration + one year; format with TO_CHAR
    expires_at::DATE + INTERVAL '1 year',
    'YYYY-MM-DD'
  )                                                AS renewal_at
FROM   subscriptions
ORDER BY days_remaining;                                                 -- Ascending days remaining, with expired rows first

/*
  Execution order (logical SQL evaluation order):
  1. FROM subscriptions                    → Read all four rows
  2. expires_at::DATE - DATE '2024-12-15'       → Calculate days remaining
  3. CASE WHEN                             → Classify each row into three statuses
  4. expires_at::DATE + INTERVAL '1 year'  → Calculate the date one year later
  5. TO_CHAR(..., 'YYYY-MM-DD')            → Format the date as text
  6. ORDER BY days_remaining               → Return rows by ascending days remaining
  */

Explanation (table transitions & key points) ▼

SELECT
  user_id, plan,
  expires_at::DATE - DATE '2024-12-15'  AS days_remaining,
  CASE
    WHEN expires_at::DATE < DATE '2024-12-15'      THEN 'Expired'
    WHEN (expires_at::DATE - DATE '2024-12-15') <= 30
                                          THEN 'Expiring Soon'
    ELSE 'Active'
  END AS status,
  TO_CHAR(expires_at::DATE + INTERVAL '1 year', 'YYYY-MM-DD') AS renewal_at
FROM   subscriptions
ORDER BY days_remaining;

LEGEND ▼

Rows read / loaded

① FROM — DATE Expiration Data ▼

FROM subscriptions (fixed reference date 2024-12-15)expires_at is stored as DATE. Subtract DATE '2024-12-15' to calculate days remaining, then use CASE WHEN to determine the status.

1 / 5

user_id	plan	expires_at
1	basic	2025-01-14
2	pro	2024-07-31
3	premium	2025-11-30
4	basic	2024-12-31

4 rows

SELECT
  EXTRACT(DAY FROM AGE(expires_at::DATE, DATE '2024-12-15'))
    AS wrong_days,
  expires_at::DATE - DATE '2024-12-15'
    AS correct_days
FROM subscriptions WHERE user_id = 3;

LEGEND ▼

Rows read / loaded

① Sample Expiration Date Spanning Several Months ▼

expires_at = '2025-11-30' (about 11.5 months later)There are 350 total days from the reference date 2024-12-15 to 2025-11-30. AGE represents this as '11 mons 15 days'. Now examine the EXTRACT(DAY FROM ...) pitfall.

1 / 3

user_id	expires_at	DATE '2024-12-15' (reference)
3	2025-11-30	2024-12-15

1 row

LEARNING POINTS

DATE differences are integers; TIMESTAMP differences are INTERVALs: DATE - DATE returns an INTEGER number of days, while TIMESTAMP - TIMESTAMP returns an INTERVAL. expires_at::DATE - DATE '2024-12-15' is the simplest way to handle days remaining as an integer. An INTERVAL can also be converted with EXTRACT(EPOCH FROM interval) / 86400, from seconds to days, but that is more verbose.

Use AGE for display and DATE subtraction for total days: AGE(ts1, ts2) returns an INTERVAL such as '1 year 3 mons 5 days'. This is useful in a human-readable UI, but EXTRACT(DAY FROM AGE(...)) retrieves only the day component, not total days. Use DATE subtraction for total-day comparisons and calculations.

Common INTERVAL forms: Write values such as INTERVAL '1 year', INTERVAL '30 days', INTERVAL '6 months', or INTERVAL '2 hours 30 minutes'. Adding or subtracting an INTERVAL from a DATE makes expiration extensions and lookback calculations straightforward.

ANTI-PATTERNS

Comparing months with EXTRACT(MONTH FROM expires_at) alone: With multi-year data, December 2024 and December 2025 are treated as the same month. Ignoring a TIMESTAMP's time zone can also shift dates between UTC and JST. Cast to DATE or specify the time zone explicitly for date comparisons.

Calculating days remaining from text: If a legacy database stores expires_at as TEXT, cast it with expires_at::DATE before calculating. Applying date operations directly to text can cause type errors or unexpected results. Store dates as DATE or TIMESTAMP from the database-design stage.

FIELD NOTE: Database Design for Expiration Management

For SaaS subscription management, consistently deriving expiration status in SQL is a robust way to automate actions such as email notifications and access restrictions. Because CURRENT_DATE is not immutable, it cannot appear in a partial-index predicate. Instead, create CREATE INDEX ON subscriptions (expires_at) to accelerate runtime predicates such as WHERE expires_at > CURRENT_DATE. Dynamically calculating status in SQL also avoids the drift risk of updating a status column in a daily batch. When time-zone support is needed, consider TIMESTAMPTZ with AT TIME ZONE 'Asia/Tokyo'.

QUESTION 8

CASE WHEN + ROUND + GREATEST — Apply Tiered Discounts and a Price Floor in One Query

CASE WHENGREATEST/LEASTTiered DiscountsReuse Expressions with CTE

Background ▼

This pattern combines multi-condition branching, lower and upper bounds, and a CTE (Common Table Expression).

-- Simple CASE form for equality: CASE value WHEN comparison THEN result
CASE customer_rank
  WHEN 'gold'   THEN 0.10   -- When customer_rank = 'gold'
  WHEN 'silver' THEN 0.05
  ELSE               0.00   -- When nothing matches; make ELSE explicit
END

GREATEST(405, 500)      -- → 500   Maximum argument, used as a lower bound
GREATEST(NULL, 500)    -- → 500   NULL is ignored in PostgreSQL
LEAST(12000, 10000)   -- → 10000 Minimum argument, used as an upper bound

Eliminate duplicate expressions with a CTE (WITH clause): Repeating CASE WHEN three times in a SELECT hurts maintainability. Calculate the discount rate once in a CTE, then reference it in the main query to write the expression only once (the DRY principle).

Problem ▼

Calculate prices from the orders table after applying a tiered discount based on customer rank.
Discount rates: platinum=20% / gold=10% / silver=5% / bronze=0%
① discount_rate: the rate for the customer's rank
② discounted: the discounted amount, rounded to an integer with ROUND
③ final_price: the final price clamped to a minimum of 500.
Use a CTE (WITH clause) to avoid repeating the CASE WHEN expression.

Table

▸ orders

order_id	customer_id	customer_rank	subtotal
1	101	platinum	15000
2	102	gold	8500
3	103	silver	3200
4	104	bronze	980
5	105	gold	450

Expected Output ▼

order_id	customer_rank	subtotal	discount_rate	discounted	final_price
1	platinum	15000	0.20	12000	12000
2	gold	8500	0.10	7650	7650
3	silver	3200	0.05	3040	3040
4	bronze	980	0.00	980	980
5	gold	450	0.10	405	500

Model Answer ▼

-- Calculate the discount rate in a CTE first to keep the CASE expression DRY
WITH ranked AS (
  SELECT
    order_id,
    customer_id,
    customer_rank,
    subtotal,
    CASE customer_rank                                          -- Simple CASE equality form, equivalent to CASE WHEN rank = 'x'
      WHEN 'platinum' THEN 0.20                                 -- Platinum: 20% discount
      WHEN 'gold'     THEN 0.10                                 -- Gold: 10% discount
      WHEN 'silver'   THEN 0.05                                 -- Silver: 5% discount
      ELSE                 0.00                                 -- Bronze or another value: no discount
    END                            AS discount_rate
  FROM   orders
)
SELECT
  order_id,
  customer_rank,
  subtotal,
  discount_rate,
  ROUND(subtotal * (1 - discount_rate))        AS discounted,   -- Round the discounted amount, using the CTE's discount_rate
  GREATEST(                                                     -- Guarantee a minimum price of 500 with a lower clamp
    ROUND(subtotal * (1 - discount_rate)),
    500
  )                                             AS final_price
FROM   ranked
ORDER BY order_id;

/*
  Execution order (logical SQL evaluation order):
  1. CTE (WITH ranked)
  2. Main query
  */

Explanation (table transitions & key points) ▼

WITH ranked AS (
  SELECT
    order_id, customer_id, customer_rank, subtotal,
    CASE customer_rank
      WHEN 'platinum' THEN 0.20
      WHEN 'gold'     THEN 0.10
      WHEN 'silver'   THEN 0.05
      ELSE 0.00
    END AS discount_rate
  FROM   orders
)
SELECT
  order_id, customer_rank, subtotal, discount_rate,
  ROUND(subtotal*(1-discount_rate)) AS discounted,
  GREATEST(
    ROUND(subtotal*(1-discount_rate)), 500
  ) AS final_price
FROM   ranked
ORDER BY order_id;

LEGEND ▼

Rows read / loaded

① FROM — Source Ranks and Subtotals ▼

FROM orderscustomer_rank has four values from 'platinum' through 'bronze'. The gold order with order_id=5 and subtotal=450 is the key price-floor case.

1 / 5

order_id	customer_rank	subtotal
1	platinum	15000
2	gold	8500
3	silver	3200
4	bronze	980
5	gold	450

5 rows

SELECT order_id, customer_rank, subtotal, discount_rate,
  ROUND(subtotal*(1-discount_rate))
    AS bad_final,
  GREATEST(ROUND(subtotal*(1-discount_rate)), 500)
    AS good_final
FROM ranked WHERE order_id = 5;

LEGEND ▼

Rows read / loaded

① Problematic Data: A Small Order ▼

A gold order with subtotal 450Applying a discount to a small order can reduce the amount below the minimum price. Examine the behavior without GREATEST.

1 / 3

order_id	customer_rank	subtotal
5	gold	450

1 row (problem case)

LEARNING POINTS

Choosing simple CASE or searched CASE: CASE rank WHEN 'gold' THEN ... is concise and specialized for equality. Use CASE WHEN condition when you need compound conditions or inequalities, as in CASE WHEN rank = 'gold' OR amount > 10000 THEN .... For equality alone, the former is usually easier to read.

PostgreSQL's GREATEST/LEAST ignore NULL: GREATEST(NULL, 500) returns 500. This resembles COALESCE and enables NULL-tolerant clamping, although all-NULL arguments return NULL. Use LEAST(discounted, 10000) for an upper clamp.

A CTE prevents repeated expressions in SELECT (the DRY principle): Writing the same CASE expression three times means fixing it in three places. Calculate it once in a CTE to centralize the logic. Multiple CTEs can also be chained as WITH a AS (...), b AS (...).

ANTI-PATTERNS

Nesting CASE WHEN until it becomes unreadable: Constructs such as CASE WHEN CASE WHEN ... END = ... THEN ... sharply reduce readability. Organize calculations into stages with a CTE or rewrite them as appropriate simple conditions.

Forgetting to clamp minimum prices or maximum discounts: Implementing only the discount logic and ignoring boundaries can produce unintended amounts for very small or very large orders. Combining GREATEST for a lower bound with LEAST for an upper bound is an essential monetary-calculation pattern.

FIELD NOTE: Discount Logic in the Database or Application Layer?

Completing discount calculations in SQL provides consistency: every client receives the same result. Calculating in the application layer makes unit tests and future logic changes easier. A common production pattern stores rank-based rates in a discount-configuration table and JOINs it in SQL. Hard-coding rates such as 0.10 in SQL requires a deployment for every change. Joining a configuration table improves maintainability by allowing rate changes without rewriting the query.

QUESTION 9

FILTER + STRING_AGG(DISTINCT) — Conditional Aggregation with Deduplication

FILTERSTRING_AGG(DISTINCT)Conditional AggregationDeduplicated Aggregation

Background ▼

This pattern combines FILTER, which applies a condition to an aggregate, with deduplicated string aggregation.

-- FILTER: apply a WHERE-like condition inside an aggregate without changing group size
COUNT(*) FILTER (WHERE status = 'completed')     -- → count completed rows only
SUM(amount) FILTER (WHERE status = 'completed')  -- → sum completed amounts only

-- STRING_AGG(DISTINCT ...): concatenate strings without duplicates
STRING_AGG(DISTINCT product_name, ', ' ORDER BY product_name)
  -- → include each product name once in lexical order
  -- Note: ORDER BY can use only the same expression as the DISTINCT argument

-- Multiply by 100.0 to use NUMERIC rather than integer division
100.0 * COUNT(*) FILTER (...) / COUNT(*)  -- → 66.7 (NUMERIC division)
100   * COUNT(*) FILTER (...) / COUNT(*)  -- → 66   (integer division truncates the fraction)

The key difference between FILTER and WHERE: WHERE removes rows before GROUP BY, changing the group's total row count. FILTER applies its condition while aggregating after grouping, so one group can calculate both the completed total and the total number of rows.

Problem ▼

Aggregate the following values by category from the sales table.
① total_sales: all rows, regardless of status
② completed_count: rows where status = 'completed', using FILTER
③ completed_amount: total amount of completed transactions, using FILTER
④ completion_rate: completion percentage, rounded to one decimal place
⑤ products: a deduplicated, lexically sorted product-name list using STRING_AGG(DISTINCT).
Sort the result by category ascending.

Table

▸ sales

sale_id	category	product_name	amount	status
1	food	Coffee	500	completed
2	food	Sandwich	800	completed
3	food	Coffee	500	cancelled
4	tech	Mouse	3000	completed
5	tech	Keyboard	8500	completed
6	tech	Mouse	3000	refunded
7	books	SQL Guide	1500	completed
8	books	Design Book	2200	cancelled

Expected Output ▼

category	total_sales	completed_count	completed_amount	completion_rate	products
books	2	1	1500	50.0	Design Book, SQL Guide
food	3	2	1300	66.7	Coffee, Sandwich
tech	3	2	11500	66.7	Keyboard, Mouse

Model Answer ▼

SELECT
  category,
  COUNT(*)                                                          AS total_sales,
  COUNT(*) FILTER (WHERE status = 'completed')                   AS completed_count,       -- Count only completed transactions without changing group size
  SUM(amount) FILTER (WHERE status = 'completed')               AS completed_amount,       -- Sum completed amounts, excluding cancellations and refunds
  ROUND(                                                                                   -- Completion rate: use NUMERIC division and round to one decimal
    100.0 * COUNT(*) FILTER (WHERE status = 'completed')
           / COUNT(*),
    1
  )                                                                   AS completion_rate,
  STRING_AGG(DISTINCT product_name, ', '                                                   -- Deduplicate and sort all product names, regardless of status
    ORDER BY product_name)                                          AS products
FROM   sales
GROUP BY category
ORDER BY category;

/*
  Execution order (logical SQL evaluation order):
  1. FROM sales                           → Read all eight rows
  2. GROUP BY category                    → Form three groups
  3. COUNT(*)                             → Count every row in each group
  4. COUNT(*) FILTER (...)                → Count completed rows only
  5. SUM(amount) FILTER (...)             → Sum completed amounts
  6. ROUND(100.0 * completed / total, 1)  → Calculate the completion rate
  7. STRING_AGG(DISTINCT ...)             → Deduplicate and concatenate in lexical order
  8. ORDER BY category                    → Return categories ascending
  */

Explanation (table transitions & key points) ▼

SELECT
  category,
  COUNT(*)                        AS total_sales,
  COUNT(*) FILTER (WHERE status='completed') AS completed_count,
  SUM(amount) FILTER (WHERE status='completed') AS completed_amount,
  ROUND(100.0 * COUNT(*) FILTER (WHERE status='completed')
    / COUNT(*), 1)    AS completion_rate,
  STRING_AGG(DISTINCT product_name, ', '
    ORDER BY product_name) AS products
FROM   sales
GROUP BY category
ORDER BY category;

LEGEND ▼

Rows read / loaded

① FROM — Sales Data with Mixed Statuses ▼

FROM salesEach category mixes completed, cancelled, and refunded rows. FILTER performs status-specific aggregates in one query.

1 / 5

sale_id	category	product_name	amount	status
1	food	Coffee	500	completed
2	food	Sandwich	800	completed
3	food	Coffee	500	cancelled
4	tech	Mouse	3000	completed
5	tech	Keyboard	8500	completed
6	tech	Mouse	3000	refunded
7	books	SQL Guide	1500	completed
8	books	Design Book	2200	cancelled

8 rows (3 categories)

SELECT category, COUNT(*) AS total_sales
FROM sales
WHERE status = 'completed'
GROUP BY category;

SELECT category, COUNT(*) AS total_sales,
  COUNT(*) FILTER(WHERE status='completed')
FROM sales GROUP BY category;

LEGEND ▼

Rows read / loaded

① FROM sales (All Eight Rows) ▼

food data (completed×2, cancelled×1)The food category has three rows: two completed and one cancelled. Compare filtering with WHERE to aggregating with FILTER.

1 / 3

sale_id	category	product_name	amount	status
1	food	Coffee	500	completed
2	food	Sandwich	800	completed
3	food	Coffee	500	cancelled

3 rows (food category)

LEARNING POINTS

FILTER is the aggregate counterpart of WHERE, but its timing differs: WHERE removes rows after FROM and before GROUP BY. FILTER applies during aggregation, so the same group can return both a total count and a conditional count. Use FILTER to consolidate pivot-like conditional aggregates into one query.

Beware fractional truncation in integer division: 100 * 2 / 3 = 66 because integer division truncates the fraction, whereas 100.0 * 2 / 3 = 66.666... uses NUMERIC division. Multiply by 100.0 when a percentage needs decimal places.

ORDER BY restriction with STRING_AGG(DISTINCT): In STRING_AGG(DISTINCT expr, sep ORDER BY expr), ORDER BY can use only an expression matching a regular DISTINCT argument. If another sort key is needed, make the required rows unique and establish that key in a subquery before applying STRING_AGG.

ANTI-PATTERNS

Distorting the total count by using WHERE instead of FILTER: Filtering with WHERE status='completed' makes total_sales equal the completed count. Use FILTER when calculating a completion rate or any metric that needs both total and conditional counts.

Expecting STRING_AGG order without ORDER BY: The concatenation order of STRING_AGG(DISTINCT ...) is nondeterministic without ORDER BY. This can produce different results in testing and production. Always specify ORDER BY column_name.

FIELD NOTE: Building a Pivot Aggregate in One Query with FILTER

FILTER's real strength is expressing a cross-tab or pivot aggregate—traditionally written with multiple queries or CASE WHEN—in one concise query. A monthly sales report can generate columns with SUM(amount) FILTER (WHERE month=1) AS jan, SUM(amount) FILTER (WHERE month=2) AS feb, .... FILTER has been available since PostgreSQL 9.4. The MySQL equivalent is SUM(CASE WHEN status='completed' THEN amount ELSE 0 END).

QUESTION 10

REGEXP_MATCHES / REGEXP_REPLACE — Extract and Transform Data from Structured Log Lines

REGEXP_MATCHESREGEXP_REPLACELog AnalysisCapture Groups

Background ▼

This is an advanced pattern for extracting and reformatting data with regular-expression capture groups ().

-- REGEXP_MATCHES: return matched groups as an array (setof text[])
REGEXP_MATCHES('ERROR 404 /api/users', '(\w+) (\d+) (.*)')
  -- → {'ERROR','404','/api/users'}  each group becomes an array element

-- Extract each group with one-based array indexes
(REGEXP_MATCHES(log, '(\w+) (\d+) (.*)'))[1]   -- → 'ERROR'    first group
(REGEXP_MATCHES(log, '(\w+) (\d+) (.*)'))[2]   -- → '404'      second group

-- REGEXP_REPLACE: reformat by referencing capture groups
REGEXP_REPLACE('20240315', '^(\d{4})(\d{2})(\d{2})$', '\1-\2-\3')
  -- → '2024-03-15'  \1=year, \2=month, \3=day

-- Match a pattern with ~, which returns BOOLEAN
'ERROR 404 /api' ~ '^(ERROR|WARN)'      -- → true  (case-sensitive)
'error 404 /api' ~* '^(error|warn)'     -- → true  (~* is case-insensitive)

Choosing REGEXP_MATCHES or REGEXP_REPLACE: REGEXP_MATCHES retrieves matched text, while REGEXP_REPLACE replaces matched text. Both support capture groups (). REGEXP_MATCHES returns no row for a nonmatch, so use LEFT JOIN, CASE, or the singular REGEXP_MATCH when nonmatching input must be retained.

Problem ▼

The app_logs table stores log messages as unstructured strings. Parse them with regular expressions and generate the following columns.
① log_level: log level (ERROR, WARN, or INFO)
② status_code: numeric HTTP status code
③ endpoint: API endpoint path
④ formatted_date: transform log_date from 'YYYYMMDD' to 'YYYY-MM-DD'
⑤ is_error: true when the log level is ERROR or WARN, using the ~ operator.
Sort the result by log_id ascending.

Table

▸ app_logs

log_id	log_date	log_message
1	20240315	ERROR 500 /api/users
2	20240315	INFO 200 /api/health
3	20240316	WARN 429 /api/orders
4	20240316	ERROR 404 /api/products
5	20240317	INFO 200 /api/users

Expected Output ▼

log_id	log_level	status_code	endpoint	formatted_date	is_error
1	ERROR	500	/api/users	2024-03-15	true
2	INFO	200	/api/health	2024-03-15	false
3	WARN	429	/api/orders	2024-03-16	true
4	ERROR	404	/api/products	2024-03-16	true
5	INFO	200	/api/users	2024-03-17	false

Model Answer ▼

SELECT
  log_id,
  (REGEXP_MATCHES(log_message, '(\w+) (\d+) (.*)'))[1]          AS log_level,  -- Split into [1] level, [2] code, and [3] path
  (REGEXP_MATCHES(log_message, '(\w+) (\d+) (.*)'))[2]::INTEGER AS status_code,           -- Cast the second group, the status-code string, to INTEGER
  (REGEXP_MATCHES(log_message, '(\w+) (\d+) (.*)'))[3]          AS endpoint,              -- Third group: endpoint path
  REGEXP_REPLACE(log_date, '^(\d{4})(\d{2})(\d{2})$', '\1-\2-\3') AS formatted_date,  -- Reformat 'YYYYMMDD' to 'YYYY-MM-DD' with capture groups
  log_message ~ '^(ERROR|WARN)'                                  AS is_error  -- Match ERROR or WARN at the start of the line
FROM   app_logs
ORDER BY log_id;

/*
  Execution order (logical SQL evaluation order):
  1. FROM app_logs        → Read all five rows
  2. REGEXP_MATCHES(...)  → Extract three regular-expression groups
  3. REGEXP_REPLACE(...)  → Reformat the date string
  4. Test log_message prefix → Classify ERROR/WARN as BOOLEAN
  5. ORDER BY log_id      → Return rows ascending
  */

Explanation (table transitions & key points) ▼

SELECT
  log_id,
  (REGEXP_MATCHES(log_message, '(\w+) (\d+) (.*)'))[1]
    AS log_level,
  (REGEXP_MATCHES(log_message, '(\w+) (\d+) (.*)'))[2]::INTEGER
    AS status_code,
  (REGEXP_MATCHES(log_message, '(\w+) (\d+) (.*)'))[3]
    AS endpoint,
  REGEXP_REPLACE(log_date,
    '^(\d{4})(\d{2})(\d{2})$', '\1-\2-\3')
    AS formatted_date,
  log_message ~ '^(ERROR|WARN)' AS is_error
FROM   app_logs
ORDER BY log_id;

LEGEND ▼

Rows read / loaded

① FROM — Unstructured Log Strings ▼

FROM app_logslog_message is an unstructured string such as 'ERROR 500 /api/users'. Capture groups split it into components. Because log_date is a 'YYYYMMDD' string, REGEXP_REPLACE reformats it.

1 / 5

log_id	log_date	log_message
1	20240315	ERROR 500 /api/users
2	20240315	INFO 200 /api/health
3	20240316	WARN 429 /api/orders
4	20240316	ERROR 404 /api/products
5	20240317	INFO 200 /api/users

5 rows

SELECT log_id, log_message,
  (REGEXP_MATCHES(log_message, '(\w+) (\d+) (.*)'))[1]
    AS bad_level,
  (REGEXP_MATCH(log_message,'(\w+) (\d+) (.*)'))[1]
    AS safe_level
FROM app_logs;

LEGEND ▼

Rows read / loaded

① Log Rows Include a Nonmatching Pattern ▼

A malformed log line appears among valid linesProduction data does not always use one format. Examine a set containing a message such as 'SYSTEM STARTUP' with no status code.

1 / 3

log_id	log_message
1	ERROR 500 /api/users
2	SYSTEM STARTUP
3	WARN 429 /api/orders

3 rows (one malformed)

LEARNING POINTS

REGEXP_MATCHES returns setof, so rows can disappear: REGEXP_MATCHES is a set-returning function with type setof text[]. Used directly in SELECT, it removes a row when the pattern does not match. A set-returning function also cannot be placed inside CASE. To preserve every input row, use the safer singular REGEXP_MATCH, available since PostgreSQL 10, which returns NULL for a nonmatch.

Singular REGEXP_MATCH returns NULL (PostgreSQL 10+): REGEXP_MATCH(str, pattern) returns NULL instead of removing a nonmatching row. It supports the same array access, such as (REGEXP_MATCH(log_message, '(\w+) (\d+) (.*)'))[1]. REGEXP_MATCH is the safer production default when one match is enough.

The ~ and ~* operators: ~ performs a case-sensitive match, and ~* is case-insensitive. !~ and !~* are their respective negations and return true for a nonmatch. Unlike LIKE, they accept flexible regular expressions such as '[0-9]+' and '^(ERROR|WARN)'.

ANTI-PATTERNS

Parsing unstructured logs with SQL alone: Where a log parser or dedicated system such as Loki or Elasticsearch exists, regular-expression parsing in SQL is less readable and less efficient. SQL regular expressions help with ad hoc analysis and data repair but are ill-suited to frequent production queries. Storing structured logs such as JSONL in JSONB improves maintainability.

Repeating the same REGEXP_MATCHES call: Calling the same pattern three times for log_level, status_code, and endpoint wastes work. An efficient design evaluates a singular REGEXP_MATCH once in a CTE, then references each element:

WITH parsed AS (SELECT log_id, REGEXP_MATCH(log_message, ...) AS m FROM app_logs) SELECT log_id, m[1] AS log_level, m[2]::INTEGER AS status_code FROM parsed

FIELD NOTE: Structured Logs and JSONB Eliminate Regular Expressions

Regular expressions over unstructured logs are most valuable for rescuing existing legacy data. In a new design, store logs as JSON from the start in a PostgreSQL JSONB column. Then log_data->>'level' and (log_data->>'status_code')::INTEGER expose structured values without regular expressions. JSONB also supports GIN indexes for searching large log volumes. Structured logging is a first step toward better observability.