It is never immediately obvious how to go from a simple SQL query to a complex one -- especially if it involves intricate calculations. One of the “

8 Steps in Writing Analytical SQL Queries

submited by
Style Pass
2024-11-08 20:00:33

It is never immediately obvious how to go from a simple SQL query to a complex one -- especially if it involves intricate calculations. One of the “dangers” of SQL is that you can create an executable query but return the wrong data. For example, it is easy to inflate the value of a calculated field by joining to multiple rows.

Let’s take a look at a sample query. This appears to look for a summary total of invoice amounts across teams. If you look closely, you might see that the joins would inflate a team’s yearly invoice spend for each team member.SELECT teams.id, json_agg(accounts.email), SUM(invoices.amount) FROM teams INNER JOIN team_members ON teams.id = team_members.team_id INNER JOIN accounts ON teams.id = team_members.team_id INNER JOIN invoices ON teams.id = invoices.team_id WHERE lower(invoices.period) > date_trunc('year', current_date) GROUP BY 1;

The query is joining invoices to teams after already joining team_members to teams. If a team has multiple members and multiple invoices, each invoice amount could be counted multiple times in the SUM(invoices.amount) calculation.Building SQL from the ground up

Leave a Comment