Lesson and practical work
Professional outcomes
- Select only fields required for a business use.
- Translate inclusion and exclusion rules into reliable filters.
- Handle dates, NULL, AND/OR precedence, and status logic.
- Order results to support review without confusing ranking with analysis.
Need a refresher?Concise syntax refresherView concise foundation
SELECT column_a, column_b
FROM table_name
WHERE condition
ORDER BY column_a DESC;SELECT controls columns, WHERE controls rows before aggregation, and ORDER BY controls presentation. The difficult part is converting policy language into exact conditions.
Build a review list for high-value Northeast customers
The account team needs customers in the Northeast who generated more than $5,000 in completed-order net revenue during Q2 and have a usable email address. “Northeast,” “completed,” “net revenue,” and “usable” all need operational definitions.
Express the population deliberately
SELECT
c.customer_id,
c.customer_name,
c.email,
SUM(o.net_revenue) AS q2_revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.region IN ('Northeast', 'Mid-Atlantic')
AND c.email IS NOT NULL
AND TRIM(c.email) <> ''
AND o.status = 'completed'
AND o.order_date >= '2026-04-01'
AND o.order_date < '2026-07-01'
GROUP BY c.customer_id, c.customer_name, c.email
HAVING SUM(o.net_revenue) > 5000
ORDER BY q2_revenue DESC;The revenue threshold appears in HAVING because it applies after customer-level aggregation. The date condition uses an exclusive upper bound, which is safer when order_date contains time.
What the result means—and does not mean
The output is a candidate review list, not proof that each customer should be contacted. Consent, account ownership, recent service issues, and customer-value context may still matter. Ordering prioritizes review; it does not create business value by itself.
Analyst's checklist
- Confirm region mapping with the business owner.
- Test AND/OR logic with known edge cases.
- Count excluded NULL and blank emails.
- Check boundary dates and timezone.
- Reconcile total qualifying revenue before exporting.
Common mistakes and failure modes
- Using = NULL instead of IS NULL.
- Writing A OR B AND C without explicit parentheses.
- Using SELECT * in a customer export.
- Filtering aggregate revenue in WHERE.
Challenge yourself
WHERE region = 'East' OR region = 'North'
AND status = 'completed'The analyst expected both regions to contain only completed orders.
- Which rows can enter unexpectedly?
- How should the condition be written?
- How would you test the correction?
Show hint
AND normally has higher precedence than OR.
Show analytical guidance
Use WHERE (region = 'East' OR region = 'North') AND status = 'completed', then compare status counts in the old and new populations.
How this may appear in an interview
What mistakes do analysts commonly make with WHERE filters?
What a strong candidate should communicate
Name NULL behavior, AND/OR precedence, timestamp boundaries, inconsistent categories, implicit type conversion, and business-rule ambiguity. Explain how you test edge cases and reconcile populations.
How would you filter a timestamp column for one calendar month?
Where this skill becomes evidence
Document the analysis population and show a compact filter-validation table: included rows, excluded statuses, missing keys, and boundary dates.
Starter SQL
SELECT
customer_id,
customer_name,
region
FROM customers
WHERE /* define the population */
ORDER BY customer_name;Test your analytical judgment
Answer before opening the guidance. More than one defensible approach may exist.
Why is SELECT * a weak default in production analysis?
Show guidance
Correct a filter that combines two regions with completed status.
Show guidance
Design edge cases for a Q2 timestamp filter.
Show guidance
Move from reviewing expert reasoning to performing the analysis.
Guided practice can add the synthetic dataset, multi-step assignment, validation checks, solution comparison, project workflow, and progress tracking.
- Work with the data
- Make and defend assumptions
- Validate the result
- Build portfolio evidence