Week 6 is here, and with it comes SQL. If you’ve spent most of your time in Tableau or Alteryx, diving into raw SQL can feel jarring. Tools like Tableau and Alteryx give you friendly drag-and-drop canvases where you can visually track your data prep and calculated fields. But under the hood, SQL is often what’s actually powering those workflows.
Without a visual canvas to guide you, writing SQL means managing all your logic by hand. Queries can get messy fast, and it’s easy to end up staring at a massive wall of text wondering where your logic went sideways.
To keep things organized and save you headaches down the road, here are five practical SQL hacks using real Formula 1 race results to help you write cleaner, more efficient queries.
1. Organize Your Logic with Common Table Expressions (CTEs)
When a query needs several layers of calculations, nesting subquery inside subquery gets confusing fast.
A Common Table Expression (CTE) lets you define a temporary, named result set at the top of your query using the WITH keyword. Think of it like a tidy prep step in Alteryx before you join everything back together.
Say we want to figure out two things: what percentage of total race points a driver earned in a specific Grand Prix, and what percentage of their entire career points that race represented.
Instead of jamming all those calculations into one massive query, we can break it into clear steps.
WITH race_points AS (
-- Total points scored across the entire grid for each race
SELECT
raceid,
SUM(points) AS total_race_points
FROM results
GROUP BY raceid
),
driver_career_points AS (
-- Total career points scored by each driver
SELECT
driverid,
SUM(points) AS career_points
FROM results
GROUP BY driverid
HAVING SUM(points) > 0
)
-- Pull it all together
SELECT
r.raceid,
r.driverid,
r.points,
ROUND((r.points::numeric / dcp.career_points) * 100, 2) AS pct_career_points,
ROUND((r.points::numeric / rp.total_race_points) * 100, 2) AS pct_race_points
FROM results r
INNER JOIN race_points rp
ON rp.raceid = r.raceid
INNER JOIN driver_career_points dcp
ON dcp.driverid = r.driverid
WHERE r.points > 0;Here we are using a Common Table Expression to determine different point statistics for F1 drivers.
By naming these chunks upfront, anyone reading your code can instantly follow your thought process. Even better, you can reuse those CTEs multiple times across the same query without recalculating them.
2. Avoid Division-by-Zero Errors with NULLIF
Dividing by zero is one of the easiest ways to break a SQL query. If a rookie driver hasn't scored any career points yet, your calculation will immediately throw an error and halt execution.
For this example you might be tempted to slap on a filter like WHERE career_points > 0, but that can accidentally drop rows you still want to see. Instead, wrap your denominator in NULLIF(column, 0).
NULLIF compares two values: if they match, it turns the value into NULL. In SQL math, dividing by NULL safely returns NULL instead of crashing your query.
SELECT
r.raceid,
r.driverid,
r.points,
-- If career_points is 0, NULLIF turns it to NULL so the query keeps running:
(r.points / NULLIF(dcp.career_points, 0)) * 100 AS pct_career_points,
(r.points / NULLIF(rp.total_race_points, 0)) * 100 AS pct_race_points
FROM results r
LEFT JOIN driver_career_points dcp
ON dcp.driverid = r.driverid
LEFT JOIN race_points rp
ON rp.raceid = r.raceid;We can use NULLIF() in the denominator to safely avoid dividing by zero.
3. Simplify Filter Testing with WHERE 1=1
When you're building and testing queries, you'll constantly toggle filters on and off to spot-check the numbers. But if your first filter sits directly after the WHERE keyword, commenting it out breaks your query:
-- This throws a syntax error because the query starts with AND:
SELECT *
FROM results
-- WHERE points > 0
AND driverid < 100;Commenting out conditions in where clauses may cause the query to break
You can simplify this by starting your filters with WHERE 1=1. Since 1 always equals 1, that line is always true, therefore every condition that follows can safely start with AND.
SELECT
raceid,
driverid,
points
FROM results
WHERE 1=1
AND points > 0
AND driverid < 100
-- AND raceid = 1052 <-- Now you can comment lines in or out without touching the WHERE clause
;Starting with 1=1 let's us test the other conditions in the where clause without breaking it.
4. Condense Metrics with Conditional Aggregation
A standard WHERE clause filters your entire query down to a specific set of rows. But what if you want to see multiple slices of data side by side, like podium finishes in the same table?
Rather than writing multiple subqueries and joining them, nest a CASE...WHEN statement directly inside your aggregate function:
SELECT
driverid,
SUM(points) AS total_career_points,
-- Pull specific buckets in a single query:
SUM(CASE WHEN position <= 3 THEN points ELSE 0 END) AS podium_points,
SUM(CASE WHEN position > 3 THEN points ELSE 0 END) AS midfield_points,
COUNT(CASE WHEN position = 1 THEN 1 END) AS total_wins
FROM results
GROUP BY driverid;We have shrunk the length of the above query by nesting the conditionals in the aggregations.
The database scans the table once and calculates all your metrics in a single pass, which is both cleaner to read and faster to run.
5. Group Dates Cleanly with DATE_TRUNC
Instead of pulling apart dates using messy string slicing or stacking multiple EXTRACT() functions, try DATE_TRUNC. It rounds any timestamp down to whatever level of detail you need (day, month, quarter, or year) while keeping it formatted as a valid date.
SELECT
DATE_TRUNC('month', race_date) AS race_month,
COUNT(raceid) AS total_races_held,
SUM(points) AS monthly_points_awarded
FROM races
GROUP BY DATE_TRUNC('month', race_date)
ORDER BY race_month DESC;
This ensures your time buckets roll up cleanly, making life much easier when you eventually plug that data back into a Tableau dashboard or reporting tool.
Hopefully these tips were helpful. If you are looking for additional resources for CTEs feel free to checkout Data Camp. They also have a free SQL course that you can use for tutorials. For SQL more broadly, Leet Code also has 50 SQL practice questions. Happy coding!