2 snippets

SQL Snippets

SQL snippets for finding duplicates, window functions and everyday queries. Tested on SQLite and DuckDB; standard syntax that also runs on PostgreSQL.

duplicates window-functions

duplicates

Find and delete duplicate rows

Count duplicated values, list the extra rows with ROW_NUMBER(), and delete them while keeping the first row per group.

-- 1. Which values appear more than once?
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

-- 2. Show the duplicate rows themselves (the first row per email is kept, the rest listed)
SELECT *
FROM (
  SELECT u.*,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM users u
) t
WHERE rn > 1;

-- 3. Delete the duplicates, keeping the lowest id per email
DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) t
  WHERE rn > 1
);

window-functions

Running total and moving average

Cumulative sums and rolling averages with SUM() and AVG() window functions, overall and per group.

-- Daily revenue with a running total and a 7-row moving average
SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total,
  AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM (
  SELECT order_date, SUM(amount) AS daily_revenue
  FROM orders
  GROUP BY order_date
) d
ORDER BY order_date;

-- Running total that restarts for each customer
SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date, id
  ) AS customer_running_total
FROM orders
ORDER BY customer_id, order_date, id;

All Languages