10 SQL Tricks I Use in My Day-to-Day Activities

Level-Up Your SQL Game!

Photo by Elizeu Dias on Unsplash

If you’ve ever stared at a complex SQL query and thought, “There’s got to be an easier way to do this!”, well, you’re not alone. We’ve all been there!

Through lots of trial and (plenty of) error, I’ve discovered some cool tricks that have become my go-to moves.

These aren’t just “fancy” SQL techniques — they’re the practical, real-world hacks that I use daily to make my work smoother, faster, and sometimes even fun.

Let’s dive in!

1. Recursion with Common Table Expressions (CTEs)

You’ve probably used CTEs to break down complex queries before, but did you know they can also be recursive? It’s like SQL’s way of doing a loop — perfect for handling hierarchical data like organizational charts or family trees. Imagine trying to figure out the reporting structure of a company — who reports to whom — and you need to go several layers deep. That’s where recursive CTEs come in!

Here’s an example:

WITH RECURSIVE OrgChart AS (
    SELECT EmployeeID, ManagerID, 1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, Level + 1
    FROM Employees e
    INNER JOIN OrgChart o ON e.ManagerID = o.EmployeeID
)
SELECT * FROM OrgChart;

Here, we’re using recursion to unravel layers of hierarchy step-by-step, starting with the big boss (whose ManagerID is NULL) and working our way down through the ranks.

Instead of writing nested queries (which is a nightmare), you let the database handle the recursion, making it both simpler and more efficient. It’s like letting SQL do all the heavy lifting while you sit back and enjoy the results.

2. JSON Functions: Because Your Data Isn’t Always Neat

Working with JSON? SQL’s got you covered! Many modern databases now allow you to parse JSON objects directly, which can be super helpful when working with semi-structured data.

Example (PostgreSQL):

SELECT 
    jsonb_each_text('{"name": "John", "age": 30}') AS (key, value);

You can pull apart a JSON object right within your SQL query, making it super easy to analyze or extract data without converting it into a separate format. SQL isn’t just for rows and columns anymore!

3. STRING_AGG(): Rolling Up Data into a String

Ever wanted to combine multiple rows of data into a single string, like making a comma-separated list of all employees in a department? SQL’s STRING_AGG() (or GROUP_CONCAT() in MySQL) is your best friend here.

Here’s an example:

SELECT Department, STRING_AGG(EmployeeName, ', ') AS Employees
FROM Employees
GROUP BY Department;

It’s like SQL is turning multiple rows into a tidy list, perfect for reports where you need a summary in one cell or column.

4. Window Functions: Moving Averages, Rankings, and Beyond

Window functions are like SQL’s magic wand. You can compute running totals, ranks, moving averages, and even look at previous or future rows without having to break your brain with crazy subqueries.

Let’s say you’re looking at employee salaries and want to calculate a rolling average of the last three salaries. Boom! Window functions to the rescue.

Here’s an example:

SELECT 
    EmployeeID,
    Salary,
    AVG(Salary) OVER (ORDER BY Salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM Employees;

Instead of writing a looping procedure or a complex join, this single query gives you the average of a sliding window of rows. It’s SQL’s way of saying, “I got you; no need to overthink this!” It’s efficient, easy to write, and super useful when working with time-series data or rankings.

5. Conditional Aggregation in GROUP BY

Ever found yourself writing multiple queries for different conditions when summarizing data? That’s unnecessary when you can do conditional aggregation. It’s like a buffet where you get to pick and choose which data you want to sum, count, or average — all in one go!

Instead of running multiple WHERE clauses to count males and females separately in a department, you can wrap your conditions inside a CASE statement.

Here’s an example:

SELECT 
    Department,
    COUNT(CASE WHEN Gender = 'M' THEN 1 END) AS MaleCount,
    COUNT(CASE WHEN Gender = 'F' THEN 1 END) AS FemaleCount
FROM Employees
GROUP BY Department;

You avoid running multiple queries, save time, and the logic is easier to follow. It’s like SQL is offering you a multi-tool — everything in one place!

6. Dynamic Pivoting with Conditional Aggregation

Pivot tables aren’t just for Excel! In SQL, you can pivot data by conditionally aggregating values, making it super easy to turn rows into columns. Think of it as SQL’s way of flipping your data sideways.

Let’s say you’re analyzing monthly sales for different departments and want to see each department’s sales for January, February, and so on — all in one row.

Here’s an example:

SELECT 
    Department,
    SUM(CASE WHEN Month = 'Jan' THEN Sales END) AS JanSales,
    SUM(CASE WHEN Month = 'Feb' THEN Sales END) AS FebSales
FROM SalesData
GROUP BY Department;

You transform your data in one clean query, without needing special pivot functions. It’s super handy for reporting purposes, and SQL handles it like a pro.

7. CROSS JOIN: Generating All Combinations

This is SQL’s version of making your data socialize! With a CROSS JOIN, you’re combining every row from one table with every row from another. Imagine you have products and categories, and you want to generate all possible product-category pairs. The CROSS JOIN lets your data mingle and create every possible combination!

Here’s an example:

SELECT p.ProductID, c.CategoryID
FROM Products p
CROSS JOIN Categories c;

It’s like SQL is inviting all your rows to a big party, and every possible pair is formed. Great for testing scenarios or generating sample data.

8. Window Functions for Cumulative Totals

Ever needed to calculate a running total in SQL? Window functions have got your back. This is perfect for financial data or tracking metrics over time. Let’s say you want to calculate the cumulative sum of transactions up to the current row.

Here’s an example:

SELECT 
    TransactionDate,
    Amount,
    SUM(Amount) OVER (ORDER BY TransactionDate) AS RunningTotal
FROM Transactions;

No complex self-joins or subqueries — just one clean function to get your cumulative totals. It’s like SQL’s version of “follow the breadcrumbs.”

9. INTERVAL for Super Simple Date Arithmetic

No more fussing with date formats or trying to figure out how many days are in a month. SQL’s INTERVAL makes date arithmetic a breeze. Want to add one month to the current date? Easy.

Here’s an example:

SELECT NOW() + INTERVAL '1 month' AS NextMonth;

Handling dates in SQL can sometimes be a pain, but with INTERVAL, it’s like SQL is handing you a calendar and saying, "Here, take as many days, months, or years as you need."

10. Temporary Indexes for Query Optimization

Sometimes, you want to speed up a query but can’t permanently alter the database schema. Enter temporary indexes. In databases like PostgreSQL, you can create an index just for one query and then drop it afterward.

Example (PostgreSQL):

CREATE INDEX ON Employees (Salary);
SELECT * FROM Employees WHERE Salary > 50000;
DROP INDEX IF EXISTS employees_salary_idx;

This gives you the power to optimize queries when needed without permanently changing the database structure. It’s like adding turbo boost for that one query you want to run faster!

---

There you go — SQL tricks that are not only powerful but also a lot of fun to use once you get the hang of them! These are sure to make your data life easier, and who doesn’t love that?

---

Thank you for reading! If you found this interesting, consider following and subscribing for more interesting articles. Catch me on LinkedIn and follow me on X (Formally Twitter).