Basic SQL for Fraud Analysis
What this domain tests
This domain tests read-only querying of claims and policy data: filtering, aggregating, joining, and spotting the errors that silently produce wrong results. Items typically ask why a query returns too many rows or inflated totals.
Core rules
- SELECT columns FROM table WHERE condition filters rows.
- GROUP BY collapses rows into groups; aggregate functions are COUNT, SUM, AVG, MIN, MAX.
- WHERE filters rows before grouping; HAVING filters groups after aggregation.
- DISTINCT removes duplicate rows; COUNT(DISTINCT claim_no) counts claims, COUNT(*) counts rows.
- INNER JOIN keeps matches only; LEFT JOIN keeps all rows from the left table and reveals unmatched keys.
- NULLs are excluded from aggregates and cannot be tested with equals; use IS NULL and COALESCE.
- CASE WHEN builds bands; subqueries and CTEs break complex logic into readable steps.
- Use LIMIT while exploring, and read-only credentials as a standing rule.
Worked example
SELECT policy_no, COUNT(DISTINCT claim_no) AS claims, SUM(paid_amt) AS paid FROM claims_payments WHERE loss_dt >= '2023-01-01' AND loss_dt < '2024-01-01' GROUP BY policy_no HAVING COUNT(DISTINCT claim_no) >= 3 AND SUM(paid_amt) > 50000 ORDER BY paid DESC. Reasoning: the WHERE clause limits the period before grouping; HAVING applies thresholds to the grouped result; DISTINCT prevents multiple payments on one claim from inflating the claim count, while SUM correctly adds every payment row.
Traps that cost marks
- Filtering aggregates in WHERE instead of HAVING.
- Using COUNT(*) on a payment table and calling it claims.
- Joining tables on non-unique keys and inflating sums.
- Testing NULL with equality so rows silently disappear.