My SQL Toolbox: Favorite Queries and Tricks for Analysts

In partnership with

SQL is one of those rare tools that stand the test of time. Whether you're a data newbie or a seasoned analyst, SQL keeps proving its versatility in slicing, dicing, and transforming data to uncover valuable insights. In this newsletter edition, I’m sharing my favorite SQL tricks and queries—ones I’ve personally relied on to solve problems, impress clients, and even speed up my workflow. These aren’t just theoretical—these are practical, real-world gems you can add to your toolbox!

1. Pivoting Data: Transform Rows into Columns

Pivoting is a common need when preparing data for analysis or reporting. While SQL doesn't have a built-in PIVOT function in all databases, you can use CASE statements to achieve a similar effect.

Pivoting data helps simplify comparisons between categories and makes data more visually digestible for presentations or reports.

Example: Show total sales by region as columns.

2. Recursive Queries: Handle Hierarchies or Chains

Recursive Common Table Expressions (CTEs) are especially handy for dealing with hierarchical data or scenarios where relationships are self-referencing, such as employee reporting structures or network paths.

Recursive queries allow you to explore relationships and hierarchical structures without manual joins or complex data wrangling.

Example: Find the reporting hierarchy for an employee.

3. String Manipulation: Clean and Format Data

SQL can handle text data too! String manipulation functions are great for standardizing messy input data or extracting useful parts of strings.

Clean, well-formatted data is critical for accurate reporting and analysis. String functions save time when preprocessing text fields.

Example: Extract the domain from email addresses.

4. Time-Based Analysis: Date Functions in Action

Working with dates and times is crucial for identifying trends or monitoring performance over time. SQL makes this easier with functions to manipulate and aggregate temporal data.

Temporal analysis reveals patterns and behaviors, helping teams make informed decisions based on time-based trends.

Example: Calculate daily revenue and highlight the busiest day.

5. Common Table Expressions (CTEs): Simplify the Complex

CTEs are lifesavers for anyone dealing with multi-step queries. They allow you to create temporary, named result sets that can be referenced within the main query. Here's why I love them: they keep things organized, reusable, and easy to debug.

Breaking down complex logic into manageable parts makes your queries cleaner and helps others (or future you) understand them.

Example: Find the top 5 customers by total spending.

6. Window Functions: Add Context to Your Data

Window functions let you perform calculations across a subset of rows without grouping. They’re incredibly useful for rankings, running totals, and comparisons.

 Window functions enable a richer level of analysis. Instead of just knowing that a product sells well, you can see how it stacks up against similar items.

Example: Rank products by sales within each category.

7. CASE Statements: Add Logic to Your Queries

The CASE statement acts as an “if-else” within your SQL queries, letting you create conditional logic on the fly.

With CASE statements, you can derive new insights or add business logic directly into your query without altering the raw data.

Example: Categorize transactions based on spending levels.

8. Indexing and Query Optimization

Writing SQL isn’t just about getting the right results—it’s also about getting them quickly. One trick I’ve learned is leveraging database indexing. Ensure that you index frequently queried columns like customer_id or transaction_date.

Optimization can save hours when dealing with large datasets. It's the difference between a query taking seconds or minutes to run.

Use EXPLAIN to understand how your query is executed:

9. Error Handling: Catching Tricky Issues

Debugging SQL can sometimes feel like chasing shadows, but good error-handling practices can save you. For example:

NULL pitfalls: Always consider how NULL values affect your calculations. Use COALESCE to handle them: 

Why SQL Tricks Matter

SQL isn’t just about writing queries; it’s about thinking critically and solving problems efficiently. A small optimization or a creative use of a feature can transform hours of frustration into a smooth workflow. These tricks have proven useful over the years.

Which of these tips resonates most with your work? Or do you have your own SQL gems to share? Let’s keep the conversation going—drop your thoughts in the comments or reach out to me!