"Page views" and "visitors" are different measures. No matter how many times the same person visits, they count as one visitor. COUNT has three forms, each counting a different target.

COUNT(*)                  -- number of rows (counts duplicates and NULLs)
COUNT(user_id)            -- rows where user_id is not NULL
COUNT(DISTINCT user_id)   -- number of distinct user_id values

access_logs and return, for each page, the "total access count (pv)" and "unique user count (uu)".

Note: /home has 4 accesses, but user_id=101 is duplicated, so there are 3 unique users.

SELECT
  page,
  COUNT(*)                AS pv,  -- total access count (number of rows)
  COUNT(DISTINCT user_id) AS uu   -- unique visitor count
FROM access_logs
GROUP BY page
ORDER BY page;

/*
  Execution order (logical evaluation order in SQL):
  1. FROM access_logs            → read all 7 rows
  2. GROUP BY page               → aggregate into 2 groups by page
  3. COUNT(*)                    → count the rows (PV) in each group
  4. COUNT(DISTINCT user_id)     → count the distinct user_id values (UU) in each group
  5. SELECT page, pv, uu         → project 3 columns
  6. ORDER BY page               → output page in ascending order
*/

Detecting duplicate data—such as an email address registered twice—is common in real work. Bundle equal values with GROUP BY, then keep only groups with "two or more rows" using HAVING COUNT(*) > 1.

GROUP BY email              -- bundle rows with the same email
HAVING COUNT(*) > 1     -- two or more rows in a group = duplicate

users table, find email addresses registered multiple times and their registration counts (cnt).

Note: tanaka@example.com and sato@example.com each appear twice. Do not include addresses that appear only once.

SELECT
  email,
  COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1  -- keep only groups with two or more rows (duplicates)
ORDER BY email;

/*
  Execution order (logical evaluation order in SQL):
  1. FROM users           → read the rows
  2. GROUP BY email       → group by email
  3. HAVING COUNT(*) > 1  → keep only duplicate groups
  4. SELECT email, cnt    → project 2 columns
  5. ORDER BY email       → sort and output
  */

After detecting duplicates, the next step is elimination. When you want to keep exactly one representative row, ROW_NUMBER() is the tool. Use PARTITION BY for the duplicate key, ORDER BY for the priority that decides which row to keep, and retain only rows where rn = 1.

ROW_NUMBER() OVER (
  PARTITION BY email            -- key used to identify duplicates
  ORDER BY created_at DESC     -- prefer the newest row (keep it)
) AS rn                          -- rn=1 is the representative row in each group

customers contains customers registered multiple times with the same email. Keep only the newest registration (the one with the latest created_at) for each email and return the unique customer list.

Note: tanaka@ex.com and sato@ex.com each appear twice. Keep the newer one. Solve it with CTE + ROW_NUMBER.

WITH ranked AS (
  SELECT
    customer_id, email, name, created_at,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC    -- the newest row gets rn=1
    ) AS rn
  FROM customers
)
SELECT
  customer_id, email, name, created_at
FROM ranked
WHERE rn = 1                  -- keep only one representative row per email
ORDER BY customer_id;

/*
  Execution order (logical evaluation order in SQL):
  1. CTE(ranked)              → read customers
  2. ROW_NUMBER() OVER (...)  → number rows newest-first within each email
  3. FROM ranked              → reference the derived table
  4. WHERE rn = 1             → keep the newest row for each email
  5. SELECT ...               → project the columns
  6. ORDER BY customer_id     → sort and output
  */

Set operations stack multiple lists vertically. UNION stacks them and then automatically removes duplicate rows, while UNION ALL keeps every row, including duplicates.

SELECT email FROM campaign_a
UNION          -- remove duplicates (equivalent to DISTINCT)
SELECT email FROM campaign_b;

-- UNION ALL keeps every duplicate row (faster because there is no removal step)

Combine the applicant emails from two campaigns (campaign_a and campaign_b) into one deduplicated list.

Note: tanaka@ex.com and sato@ex.com applied to both campaigns. Collapse the duplicates into one row.

SELECT email FROM campaign_a
UNION                       -- combine both lists and remove duplicates
SELECT email FROM campaign_b
ORDER BY email;

/*
  Execution order (logical evaluation order in SQL):
  1. SELECT FROM campaign_a  → read the upper list
  2. SELECT FROM campaign_b  → read the lower list
  3. UNION                   → stack vertically and remove duplicates
  4. ORDER BY email          → sort the combined result
  */