5 SQL Tricks Worth Remembering Before You Close the Laptop

Disclosure: this post may contain links to books as an affiliate link. If you purchase through it, this site may earn a small commission at no extra cost to you.

It is Friday. The sprint is done, the deploys are out, and nobody is scheduling a meeting in the next 30 minutes. This one is short — five SQL tricks that are genuinely useful, easy to forget, and satisfying to rediscover. No deep dives, no architecture decisions. Just good SQL to carry into next week.

1. Fill missing dates in a report with generate_series (PostgreSQL)

Your daily sales query skips dates with zero transactions. Instead of patching this in application code, generate a full date spine in the database and left join your data against it. The generate_series function handles this cleanly — no temp tables, no loops.

SELECT
    d.day::date,
    COALESCE(SUM(s.amount), 0) AS total_sales
FROM
    generate_series(
        '2025-05-01'::timestamp,
        '2025-05-31'::timestamp,
        '1 day'::interval
    ) AS d(day)
LEFT JOIN sales s
    ON s.sale_date = d.day::date
GROUP BY d.day
ORDER BY d.day;
  

2. Get the last day of any month without a calendar lookup (SQL Server)

Month-end reporting always needs the last day of the month, and it changes every month, and February exists. EOMONTH handles leap years, 30-day months, and everything else automatically. The optional second argument lets you offset forward or backward.

-- Last day of the current month
SELECT EOMONTH(GETDATE())  -- 2025-05-31
 
-- Last day of the previous month
SELECT EOMONTH(GETDATE(), -1) -- 2025-04-30
 
-- First day of the current month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) -- 2025-05-01
  

3. Use a CTE to avoid repeating yourself (PostgreSQL & SQL Server)

If you find yourself copy-pasting the same subquery twice in one statement, a Common Table Expression cleans it up immediately. CTEs are not just for readability — they also prevent you from calculating the same thing twice and getting different results if the underlying data changes mid-query.

WITH monthly_totals AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount)                       AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    ROUND(revenue * 100.0 / SUM(revenue) OVER (), 2) AS pct_of_total
FROM monthly_totals
ORDER BY month;
  

4. Spot duplicate rows instantly with GROUP BY and HAVING

Before running a deduplication job, it pays to know exactly which keys are duplicated and how many copies exist. This pattern works identically across PostgreSQL, SQL Server, MySQL, and Redshift — no library needed, just standard SQL.

SELECT
    customer_id,
    email,
    COUNT(*) AS occurrences
FROM customers
GROUP BY
    customer_id,
    email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;
  

Add more columns to the GROUP BY to tighten the definition of "duplicate" for your specific case. The result set tells you exactly what to target before touching any data.

5. LAG() to compare a row against the previous one (PostgreSQL & SQL Server)

Week-over-week, day-over-day, month-over-month — any time you need to compare a metric against the prior row, LAG() saves you a self-join. It is a window function available in PostgreSQL, SQL Server, MySQL 8+, and Redshift.

SELECT
    sale_date,
    daily_revenue,
    LAG(daily_revenue) OVER (ORDER BY sale_date)
        AS prev_day_revenue,
    ROUND(
        (daily_revenue - LAG(daily_revenue) OVER (ORDER BY sale_date))
        * 100.0
        / NULLIF(LAG(daily_revenue) OVER (ORDER BY sale_date), 0),
    2) AS pct_change
FROM daily_sales
ORDER BY sale_date;
  

The NULLIF(..., 0) wrapper on the denominator prevents a division-by-zero error on days where the previous revenue was zero — a small detail that saves a lot of Friday-afternoon debugging.

Wrapping Up

None of these are exotic — generate_series, EOMONTH, CTEs, HAVING, and LAG() are bread-and-butter SQL. They just tend to get forgotten under deadline pressure and rediscovered on a quiet Friday. Keep them close. The one worth memorizing cold: NULLIF on every division denominator. Division by zero is always the error you find in production.

If this kind of thing clicks for you and you want more of it — the patterns, the thinking behind the queries, the instinct for when to reach for a window function versus a subquery — Practical SQL, 2nd Edition by Anthony DeBarros is one of the best books on the subject. It is written for people who want to actually understand their data, not just run queries.