-- Without DISTINCT: may return duplicate values
SELECT department_id FROM employees;
-- 1, 1, 2, 2, 2, 3

-- With DISTINCT: one row per unique value
SELECT DISTINCT department_id FROM employees;
-- 1, 2, 3

-- Common uses
SELECT DISTINCT country FROM customers;
SELECT DISTINCT status  FROM orders;
SELECT DISTINCT category_id FROM products WHERE active = 1;

-- Unique (city, country) combinations
SELECT DISTINCT city, country FROM customers;
-- 'New York', 'US' and 'New York', 'CA' are NOT duplicates

-- Example data:
-- New York    | US
-- New York    | CA   <-- different from row 1 (different country)
-- Los Angeles | US
-- London      | GB
-- New York    | US   <-- duplicate of row 1, eliminated

-- Result: 4 rows (the second 'New York, US' is removed)

-- These two queries produce identical results:
SELECT DISTINCT department_id FROM employees ORDER BY department_id;

SELECT department_id FROM employees GROUP BY department_id ORDER BY department_id;

-- GROUP BY wins when you need aggregates alongside unique values:
SELECT department_id,
       COUNT(*)       AS headcount,
       AVG(salary)    AS avg_salary,
       MAX(salary)    AS max_salary
FROM employees
GROUP BY department_id;
-- DISTINCT cannot produce aggregates -- use GROUP BY for this pattern

-- How many distinct products were ever ordered?
SELECT COUNT(DISTINCT product_id) AS unique_products_ordered
FROM order_items;

-- Monthly unique active customers (common KPI)
SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS month,
  COUNT(DISTINCT customer_id)      AS unique_customers
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month;

-- Multiple distinct counts in one query
SELECT
  COUNT(DISTINCT customer_id) AS unique_customers,
  COUNT(DISTINCT product_id)  AS unique_products,
  COUNT(*)                    AS total_order_lines
FROM order_items;

-- Approximate distinct count on very large tables (MySQL 8.0+)
-- HyperLogLog-style: approx_count_distinct is not built-in,
-- but COUNT(DISTINCT) on indexed columns is fast via loose index scan

CREATE TABLE test_null (val INT);
INSERT INTO test_null VALUES (1), (2), (NULL), (NULL), (1);

SELECT DISTINCT val FROM test_null;
-- Result:
-- 1
-- 2
-- NULL   <-- only one NULL, even though two were inserted

-- COUNT(DISTINCT) ignores NULLs entirely
SELECT COUNT(DISTINCT val) FROM test_null;
-- Returns 2 (only non-NULL distinct values: 1 and 2)

-- ORDER BY columns must appear in the SELECT list when using DISTINCT
-- This works:
SELECT DISTINCT department_id, last_name
FROM employees
ORDER BY department_id, last_name;

-- This FAILS in strict mode (ordering by a column not in SELECT):
SELECT DISTINCT department_id
FROM employees
ORDER BY last_name;  -- ERROR: 'last_name' not in DISTINCT select list

-- Check the execution plan for DISTINCT
EXPLAIN SELECT DISTINCT department_id FROM employees;

-- Good outcome: 'Using index' means a covering index eliminates the sort
-- Bad outcome: 'Using temporary; Using filesort' means extra memory and disk work

-- Create a covering index to improve DISTINCT performance
ALTER TABLE employees ADD INDEX idx_dept (department_id);
-- Now DISTINCT on department_id uses an index-only scan (no filesort)

-- Verify the improvement
EXPLAIN SELECT DISTINCT department_id FROM employees;
-- Extra: 'Using index'  (no more 'Using temporary')

-- Scenario: unique customers who spent more than $500 TOTAL across all orders
-- DISTINCT does NOT work for aggregate filtering:
SELECT DISTINCT customer_id FROM orders WHERE total > 500;
-- This gives customers with ANY single order > $500, not total spending

-- GROUP BY + HAVING is the correct approach:
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 500;

-- Unique product categories that have at least 10 products
SELECT category_id
FROM products
GROUP BY category_id
HAVING COUNT(*) >= 10;

-- Cannot be done with DISTINCT alone; GROUP BY HAVING is required

-- Keep only the most recent order per customer
-- DISTINCT customer_id would lose the order details
SELECT customer_id, order_id, total, created_at
FROM (
  SELECT
    customer_id,
    id AS order_id,
    total,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;

-- Most recently viewed product page per user session
SELECT session_id, page_slug, viewed_at
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY viewed_at DESC) AS rn
  FROM page_views
) r
WHERE rn = 1;

-- Find categories that have at least one active product
SELECT * FROM categories
WHERE id IN (
  SELECT DISTINCT category_id FROM products WHERE active = 1
);

-- The DISTINCT in the subquery can help performance by reducing rows passed upward
-- though the optimizer may remove it automatically in EXISTS-style rewrites

-- EXISTS is often faster for this pattern:
SELECT * FROM categories c
WHERE EXISTS (
  SELECT 1 FROM products p
  WHERE p.category_id = c.id AND p.active = 1
);

-- GROUP_CONCAT with DISTINCT: unique values in a comma-separated list
SELECT
  department_id,
  GROUP_CONCAT(DISTINCT job_title ORDER BY job_title SEPARATOR ', ') AS unique_roles
FROM employees
GROUP BY department_id;

-- SUM(DISTINCT col): sum only unique values (uncommon but valid)
SELECT SUM(DISTINCT price) AS sum_of_unique_prices FROM products;

-- AVG(DISTINCT col): average of unique values
SELECT AVG(DISTINCT salary) AS avg_unique_salary FROM employees;

-- Monthly Unique Active Users (MAU) — a standard product metric
SELECT
  DATE_FORMAT(event_date, '%Y-%m')  AS month,
  COUNT(DISTINCT user_id)           AS mau
FROM user_activity_events
WHERE event_type = 'session_start'
  AND event_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(event_date, '%Y-%m')
ORDER BY month;

-- Weekly unique users who performed a purchase
SELECT
  YEARWEEK(created_at, 3)     AS iso_week,
  COUNT(DISTINCT customer_id) AS weekly_buyers
FROM orders
WHERE status = 'completed'
GROUP BY YEARWEEK(created_at, 3)
ORDER BY iso_week DESC
LIMIT 12;

-- Find duplicate emails in the users table
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

-- Find the actual duplicate rows (keep the first, show the extras)
SELECT u.*
FROM users u
JOIN (
  SELECT email, MIN(id) AS keep_id
  FROM users
  GROUP BY email
  HAVING COUNT(*) > 1
) dups ON u.email = dups.email
WHERE u.id != dups.keep_id;

-- Delete duplicates, keeping the row with the lowest id
DELETE u
FROM users u
JOIN (
  SELECT email, MIN(id) AS keep_id
  FROM users
  GROUP BY email
  HAVING COUNT(*) > 1
) dups ON u.email = dups.email
WHERE u.id != dups.keep_id;

-- Tip 1: use a loose index scan for DISTINCT on indexed columns
-- MySQL can satisfy DISTINCT on a single indexed column by walking
-- only the first entry per distinct value in the B-tree
ALTER TABLE orders ADD INDEX idx_status (status);

EXPLAIN SELECT DISTINCT status FROM orders;
-- Extra: 'Using index'  (loose index scan -- no sort, no temp table)

-- Tip 2: prefer DISTINCT on narrow columns to reduce sort overhead
-- DISTINCT on a TEXT column requires sorting all text values
-- DISTINCT on an INT is much cheaper

-- Tip 3: push filters into WHERE before DISTINCT to reduce the dedup work
-- BAD: deduplicate 1M rows then filter
SELECT DISTINCT customer_id
FROM orders
WHERE status = 'completed'  -- filter before dedup is fine here

-- BAD pattern: applying DISTINCT to an already-unique column
-- (wastes CPU with no benefit)
SELECT DISTINCT id FROM orders;  -- id is the PK, already unique

-- Tip 4: verify the optimizer plan
EXPLAIN SELECT DISTINCT department_id, job_title FROM employees;
-- If 'Using temporary; Using filesort' -> add composite index on (department_id, job_title)

-- Track how many distinct users viewed each product each day
CREATE TABLE product_views (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id INT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED NOT NULL,
  viewed_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_product_date (product_id, viewed_at)
);

-- Daily unique viewers per product (last 7 days)
SELECT
  product_id,
  DATE(viewed_at)             AS view_date,
  COUNT(DISTINCT user_id)     AS unique_viewers
FROM product_views
WHERE viewed_at >= CURDATE() - INTERVAL 7 DAY
GROUP BY product_id, DATE(viewed_at)
ORDER BY product_id, view_date;

-- Top 10 products by unique viewers today
SELECT
  product_id,
  COUNT(DISTINCT user_id) AS unique_viewers_today
FROM product_views
WHERE viewed_at >= CURDATE()
GROUP BY product_id
ORDER BY unique_viewers_today DESC
LIMIT 10;

-- Query returns duplicate customers unexpectedly
SELECT DISTINCT c.id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01';
-- DISTINCT is suppressing duplicates caused by multiple orders per customer

-- Root cause: JOIN produces one row per order, not per customer
-- Fix 1: use EXISTS to check if any matching order exists
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
    AND o.created_at >= '2024-01-01'
);

-- Fix 2: aggregate at the order level if you need order data
SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01'
GROUP BY c.id, c.name;

-- DISTINCT and window functions require careful composition
-- Cannot use DISTINCT inside a window function (unsupported syntax):
-- SELECT COUNT(DISTINCT user_id) OVER (PARTITION BY product_id) -- ERROR

-- Workaround: use subquery/CTE with conditional aggregation
WITH daily_views AS (
  SELECT
    product_id,
    DATE(viewed_at)         AS view_date,
    COUNT(DISTINCT user_id) AS unique_viewers
  FROM product_views
  GROUP BY product_id, DATE(viewed_at)
)
SELECT
  product_id,
  view_date,
  unique_viewers,
  SUM(unique_viewers) OVER (PARTITION BY product_id ORDER BY view_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day_viewers
FROM daily_views
ORDER BY product_id, view_date;