SQL DISTINCT and Deduplication Practice

SELECT DISTINCT city, state FROM customers

Removes duplicate rows from the result set. Operates on all selected columns together, not just the first one. SELECT DISTINCT city, state returns unique (city, state) pairs, not unique cities and unique states separately.

Common use cases

• Get unique values from a column for exploration
• Remove exact duplicate rows from a result
• Count unique categories or types
• Build a reference list from transactional data

SELECT DISTINCT ON (user_id) user_id, event_type, created_at
FROM events
ORDER BY user_id, created_at DESC

Postgres-specific. Returns the first row for each unique value of the specified columns, based on the ORDER BY. This is the fastest way to get 'the most recent row per group' in Postgres. Not available in BigQuery, Snowflake, or MySQL.

Common use cases

• Most recent event per user (faster than ROW_NUMBER in Postgres)
• Latest price per product
• First login per user per day
• Dedup rows keeping the most complete version

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY user_id ORDER BY updated_at DESC
  ) AS rn
  FROM users
)
SELECT * FROM ranked WHERE rn = 1

The most portable deduplication pattern. Works in every SQL dialect. Assign a row number within each group, ordered by your preference (most recent, highest priority, etc.), then filter to rn = 1.

Common use cases

• Keep the most recent record per entity
• Deduplicate based on a priority column
• Pick one row per group with full control over which one
• Clean up tables with multiple versions of the same record

SELECT user_id, MAX(updated_at) AS latest_update, MAX(email) AS email
FROM users
GROUP BY user_id

Uses aggregation to collapse duplicates. Works well when you need a few specific columns and can aggregate the rest meaningfully (MAX for most recent, MIN for earliest, ANY_VALUE for identical values).

Common use cases

• Collapse multiple rows into one per key
• Get the latest timestamp per entity
• Merge duplicate records by taking MAX/MIN of each field
• Simple dedup when you do not need all original columns

SELECT COUNT(DISTINCT user_id) AS unique_users FROM events

Counts unique values in a column, ignoring NULLs. This is the standard way to calculate unique counts (unique visitors, unique products sold, etc.). Can be expensive on large datasets because it requires sorting or hashing all values.

Common use cases

• Count unique users, products, or sessions
• Calculate distinct counts within groups
• Compare total events vs unique users
• Measure cardinality of a column

A customers table has duplicate rows due to a broken ETL job. Each row has (customer_id, name, email, phone, updated_at). Some customers have 2-5 duplicate rows with different updated_at values. Write a query that returns exactly one row per customer: the one with the latest updated_at.

An events table has (user_id, event_type, event_timestamp). A user who triggers 50 events in a day should count as 1 DAU. Write a query that returns the DAU count for each day in the last 30 days. Handle days with zero activity.

A products table has (id, name, price, category). Some products are near-duplicates: same category, same price, and names that differ only in casing or trailing whitespace. Write a query that identifies all near-duplicate groups and keeps only the row with the lowest id.

Given orders (order_id, customer_id, product_id) and customers (customer_id, segment), calculate the number of distinct products purchased by each customer segment. Then find which product is purchased by the most segments.

An events table has (user_id, event_type, event_timestamp). Events within 30 minutes of each other belong to the same session. Some events are exact duplicates (same user, type, and timestamp). First deduplicate exact duplicates, then assign session IDs, then return session_id, user_id, session_start, session_end, and event_count per session.