Two rows: ' Anna@GMAIL.com ' and 'anna@gmail.com'. Same customer? Your JOIN says no — trailing space, different case, zero matches. Half of real-world SQL isn't clever logic; it's scrubbing values until equal things are actually equal, and bucketing timestamps until 'March' means March.
What you'll learn
What
String functions transform text — case (LOWER/UPPER), whitespace (TRIM), slicing (SUBSTRING, SPLIT_PART), assembly (CONCAT, ||), and search (POSITION, REPLACE). Date functions do calendar math — CURRENT_DATE, intervals, AGE — and reshape granularity: EXTRACT pulls parts out, DATE_TRUNC rounds timestamps down to a period.
Why
Text keys join reports together and timestamps drive every trend line. Unnormalized text silently breaks joins, GROUP BYs, and dedup; naive date handling double-counts boundary days and shreds monthly rollups into per-second groups. These functions are how SQL analysts spend a large share of their week — fluency here is fluency, period.
Where it's used
Email/ID normalization before joins, name parsing, URL and SKU dissection, cohort assignment by signup month, 'last 30 days' filters, and the GROUP BY DATE_TRUNC('month', …) at the heart of every revenue chart.
Where this runs in production
Contact matching normalizes emails (lowercase, trimmed, dots and plus-tags handled) before comparing — string functions are the first stage of every identity-resolution pipeline.
Trip timestamps are truncated to the hour and extracted by day-of-week to build the demand heatmaps that drive surge pricing and driver incentives.
Marketing dashboards split landing-page URLs on '?' and '&' with SPLIT_PART to recover utm_source and utm_campaign from raw traffic logs.
Sorting socks works only after washing: dried mud makes identical socks look different. Text data arrives muddy — stray spaces, RaNdOm case, typo'd separators — and every comparison (JOIN, GROUP BY, DISTINCT, =) sorts by exact bytes. String functions are the wash cycle. Analysts who join first and wash later spend the afternoon wondering why half their customers 'disappeared'.
SELECT
SUBSTRING(sku FROM 1 FOR 3) AS line_code, -- 'ELC'
LEFT(sku, 3) AS line_code2, -- same, shorter
SPLIT_PART(email, '@', 2) AS domain, -- 'gmail.com'
POSITION('@' IN email) AS at_index -- 5
FROM customers;
-- sku = 'ELC-4412-EU', email = 'anna@gmail.com'Slicing toolbox. SUBSTRING takes start/length (1-indexed!); LEFT/RIGHT grab edges; SPLIT_PART splits on a delimiter and returns the Nth piece — the workhorse for emails, URLs, and composite SKUs; POSITION finds where a substring sits (0 = absent). Dialect note: SPLIT_PART is PostgreSQL/Redshift/Snowflake; MySQL uses SUBSTRING_INDEX.
|| joins strings: first_name || ' ' || last_name. But NULL is contagious through ||: if middle_name is NULL the whole result is NULL — a report of vanished names. CONCAT(a, b, c) treats NULLs as empty strings instead, and COALESCE(col, '') inoculates any single piece. REPLACE(col, '-', '') strips characters (phone numbers, formatted IDs) — a normalization staple beside LOWER/TRIM.
DATE_TRUNC('month', created_at) rounds every timestamp down to its month's first instant — 2026-07-14 09:31:22 → 2026-07-01 00:00:00. Group by that and July's thousands of orders form ONE group. 'week', 'day', 'hour', 'quarter', 'year' work the same. This is the single most-used function in analytics SQL: every monthly revenue chart is GROUP BY DATE_TRUNC('month', …) wearing a suit. (MySQL lacks it: DATE_FORMAT(dt, '%Y-%m-01') is the idiom.)
SELECT EXTRACT(YEAR FROM created_at) AS yr, -- 2026 EXTRACT(MONTH FROM created_at) AS mon, -- 7 EXTRACT(DOW FROM created_at) AS dow, -- 0=Sunday (PG) TO_CHAR(created_at, 'YYYY-MM') AS month_label, -- '2026-07' TO_CHAR(created_at, 'Day') AS day_name -- 'Tuesday' FROM orders;
EXTRACT pulls one numeric field out — for cyclical questions ('which weekday sells most?') where all Julys across years should pool together. TO_CHAR formats for humans and labels. Choose deliberately: EXTRACT(MONTH …) merges 2025-07 with 2026-07; DATE_TRUNC('month', …) keeps them separate. One answers seasonality, the other answers trends.
WHERE created_at BETWEEN '2026-07-01' AND '2026-07-31' silently loses almost all of July 31: the string coerces to midnight 00:00:00, so 31st 09:15 is out of range. The professional pattern is half-open: created_at >= '2026-07-01' AND created_at < '2026-08-01' — every July instant in, nothing double-counted at the boundary, and it works identically for days, months, and years. Reserve BETWEEN for pure DATE columns, and even then half-open habits never hurt.
The same value — 2026-07-14 09:31:22 — transformed for different jobs. Click each node to see which question each shape answers.
Count how many customers use a Gmail address — in a table where emails arrive in every case and padding imaginable.
TRIM strips the invisible spaces that defeat LIKE's end-anchor ('...com ' doesn't end with 'com'); LOWER folds 'Anna@GMAIL.com' to comparable form. The inside-out order — trim, then lower, then compare — is the standard wash cycle. Without it, this count silently undercounts.
gmail_users
-------------
418
(1 row)Your task
Build a June signup report from users(user_id, email, signup_at): return the email domain (domain, via SPLIT_PART on the normalized email) and signups per domain — counting only signups in June 2026 using a half-open range. Sort by signups descending, then domain. Fill in the blanks.
domain | signups -----------+--------- gmail.com | 2 yahoo.com | 1 (2 rows)
Write your solution in the editor on the right, then hit Run.
Which expression reliably normalizes an email for joining?
What does DATE_TRUNC('month', TIMESTAMP '2026-07-14 09:31:22') return?
first_name || ' ' || middle_name || ' ' || last_name — what happens when middle_name is NULL?
Why does WHERE created_at BETWEEN '2026-07-01' AND '2026-07-31' miscount July for a TIMESTAMP column?
What rewrite fixes the performance, and why?
From support_tickets(ticket_id, customer_email, created_at, closed_at), produce a per-month report: month (as 'YYYY-MM' text), tickets opened (opened), and average resolution days for closed tickets (avg_days, one decimal, computed from closed_at - created_at). Group and order by month. Note: closed_at is NULL for open tickets — AVG must skip them (it does so naturally).
Two tables must join on email, but the data is messy. Walk me through making that join trustworthy.
First I profile the mess: COUNT rows where email <> LOWER(TRIM(email)) on each side to size the case and whitespace problems, and eyeball a sample for structural issues (plus-tags, dots in Gmail locals, obvious typos). The core fix is symmetric normalization — LOWER(TRIM(email)) applied to BOTH sides, defined once in a CTE per side so the recipe can't drift between queries; washing only one side is washing neither. Depending on the business, I may go further: stripping +tags (SPLIT_PART on '+') or Gmail dot-collapsing — each an explicit, documented decision because they change match semantics. Then I verify with counts: matched rows before vs after normalization, and an anti-join sample of remaining non-matches to see what class of mismatch survives (typos need fuzzy matching, which is a different project). Finally, for a recurring join I'd push normalization to write time — a stored email_normalized column, possibly with an expression index — so every consumer inherits clean keys instead of re-washing.
Explain the difference between DATE_TRUNC and EXTRACT, with a case where confusing them gives a wrong answer.
DATE_TRUNC rounds a timestamp down to a period boundary and KEEPS the full date type — July 14th 2026 becomes July 1st 2026 — so different years stay distinct; it's the trend shape. EXTRACT pulls out one numeric component — month 7 — discarding the year, so all Julys ever pool together; it's the seasonality/cycle shape. The classic confusion: asked for monthly revenue growth, an analyst groups by EXTRACT(MONTH FROM created_at) over two years of data. The 'July' bar now silently sums July 2025 AND July 2026; the chart shows twelve points where twenty-four months of history exist, growth is unmeasurable, and totals look inflated relative to any single year. Nothing errors — the numbers are just answering a different question. My habit: the words 'over time' in a request mean DATE_TRUNC; the words 'which month/day/hour typically' mean EXTRACT.
What is a sargable predicate, and how does it interact with the date and string functions from this toolbox?
Sargable — from 'Search ARGument able' — describes a predicate written so the engine can use an index seek: the raw column stands alone on one side of the comparison, and the constant work happens on the other side. WHERE created_at >= '2026-07-01' AND created_at < '2026-08-01' is sargable; WHERE DATE_TRUNC('month', created_at) = '2026-07-01' is not, because the function must run on every row before comparing, forcing a scan. The transformation functions in this lesson are the main way analysts accidentally break sargability: LOWER(email) =, TO_CHAR(dt,…) =, SUBSTRING(sku,…) = all hide the column. The remedies, in order of preference: move the function to the constant side or rewrite as a range (dates truncate beautifully into half-open ranges); create an expression index matching the exact wrapped form (ON t (LOWER(email))); or materialize a normalized column at write time. In analytics warehouses without B-tree indexes the same idea survives as partition pruning — a filter on the raw partition column prunes files, a function-wrapped one doesn't.
Common Mistakes to Avoid
1) Joining or grouping on unwashed text — case and padding differences quietly split one entity into many; normalize with LOWER(TRIM(…)) on BOTH sides. 2) BETWEEN on timestamp ranges — the upper date coerces to midnight and loses the last day; use half-open >= / <. 3) Concatenating with || across nullable columns — one NULL nullifies the whole string; use CONCAT or COALESCE. 4) EXTRACT(MONTH …) for a trend across years — Julys pool together; trends need DATE_TRUNC. 5) Wrapping the filtered column in functions inside WHERE — kills index use; keep predicates sargable with ranges on raw columns.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: why text normalization matters, with a laundry analogy of your own.' • 'Quiz me: messy strings, I write the wash expression; timestamps, I pick TRUNC vs EXTRACT.' • 'Show me the BETWEEN-loses-a-day bug with three timestamps and the half-open fix.' • 'Trace SPLIT_PART over a URL with utm parameters, piece by piece.' • 'Interview mode: make a slow function-wrapped date filter sargable and explain why it works.'
Glossary
LOWER / UPPER — case folding. TRIM / LTRIM / RTRIM — strip whitespace (or given characters) from ends. LENGTH — character count; padding detector. SUBSTRING / LEFT / RIGHT — positional slicing (1-indexed). SPLIT_PART — nth piece after splitting on a delimiter. POSITION — 1-based index of a substring, 0 if absent. REPLACE — substitute every occurrence of a substring. || vs CONCAT — concatenation; || propagates NULL, CONCAT treats it as ''. Interval — a duration value for date arithmetic. Half-open range — >= start AND < end; boundary-exact filtering. DATE_TRUNC — round a timestamp down to a period start (trend shape). EXTRACT — pull one numeric part (seasonality shape). TO_CHAR — format dates/numbers as text labels. AT TIME ZONE — explicit timezone conversion. Sargable — predicate form that permits index seeks; broken by wrapping the column in functions. Expression index — an index over a computed expression like LOWER(email).
Recommended Resources
• Docs: PostgreSQL manual — 'String Functions and Operators' and 'Date/Time Functions and Operators' (bookmark both; nobody memorizes them all). • Read: 'Falsehoods Programmers Believe About Time' for healthy timezone paranoia. • Practice: take one messy CSV you own, load it, and build a clean_* CTE: washed keys, split composites, truncated months — then a trend query and a weekday-seasonality query on the same column. • Next in DSM: you can now clean and reshape any column — Database Design Concepts opens the Design module, showing how schemas are structured so there's less mess to clean in the first place.
Recap
✓ Normalize text before any comparison: LOWER(TRIM(col)) on both sides of a join, REPLACE for stray characters. ✓ Slice with SUBSTRING/LEFT/RIGHT and SPLIT_PART; find with POSITION; assemble with CONCAT (NULL-safe) rather than || across nullables. ✓ Date arithmetic runs on intervals; filter time ranges half-open (>= start, < end) — BETWEEN loses the last day on timestamps. ✓ DATE_TRUNC buckets timestamps for trends (years stay distinct); EXTRACT pulls parts for seasonality (years pool); TO_CHAR labels at the end. ✓ Keep predicates sargable — functions around the filtered column force full scans; ranges on raw columns let indexes work. ✓ Timezones: know which zone your 'day' means and convert explicitly. Next up: Database Design Concepts. You've mastered querying data as it is — the Design module asks how it SHOULD be: entities, keys, relationships, and the modeling decisions that decide whether future analysts inherit clean tables or a cleaning shift.
Run your code to see the output here.