Overview

This article contains the Gtmhub best practices for writing insight SQL.

General Points

  • Capitalize all SQL key words to distinguish them from specific tables/columns/aliases

    • SELECT, FROM, WHERE, ON, AS, etc.

  • Avoid using COUNT(*). Use COUNT(1) instead

  • Avoid using SELECT * except when testing. Use a specific list of columns you want instead

  • Avoid using SELECT DISTINCT if possible due to efficiency. If possible, use it on smaller tables

  • Avoid using temporary tables using a WITH clause. They should be used as a last resort only

Table Aliases

  • Unless selecting from only one table, alias your table and add the alias to all columns referenced

    • This includes both in the SELECT part of the statement as well as JOIN and WHERE clauses

    • This will avoid confusion about what table columns are coming from

  • Table aliases should be either the full table name or an abbreviation representing the table name

  • If a table is referenced multiple times, use a number on the end of the alias

Column Aliases

  • All columns must include an alias unless you are using the name from the original table and it is not ambiguous as to what it belongs to

    • For example, if joining metrics and goals, you should use goal_name and metric_name instead of simply “name”

    • All columns using aggregation or other function manipulation must be aliased

  • Column aliases should use lower case letters and underscores only. Do not use camel case

    • When converted into a JavaScript object, all column names are converted to lower case. If the SQL aliases them to upper case, it can lead to confusion when trying to reference the column

❌Avoid This

SELECT g.name,
m.name AS name1,
ms.createdat AS snapshotDate
FROM gtmhubgoals g
INNER JOIN
gtmhubmetrics m
ON g.id = m.goalid
INNER JOIN
gtmhubmetricsnapshots ms
ON ms.metricid = m.id

✔️Do This

SELECT g.name AS objective_name,
m.name AS kr_name,
ms.createdat AS snapshot_date
FROM gtmhubgoals g
INNER JOIN
gtmhubmetrics m
ON g.id = m.goalid
INNER JOIN
gtmhubmetricsnapshots ms
ON ms.metricid = m.id

Joins

  • When joining tables together, use INNER JOIN wherever possible instead of LEFT JOIN

    • INNER JOINs are more efficient because they will remove rows, meaning fewer comparisons in later joins

  • When ordering tables in JOINs, start with smaller tables and then move to progressively larger tables

    • I.E. start with objectives, then join to Key Results, then join to Key Result Snapshots

    • This reduces the number of comparisons that need to occur to complete all joins

  • Do not use nested queries if you can avoid it. A simple JOIN is significantly more efficient

  • When ordering a mix of inner and left JOINs, do all inner joins first, then left joins

    • You can get unexpected results when mixing inner and left joins

    • Completing inner joins first means there are fewer rows for comparison on the left joins

  • Do not use RIGHT JOIN, re-order your join order to be able to use a LEFT JOIN instead

    • Mixing RIGHT and LEFT joins can be confusing and difficult to follow. In almost all situations, you should be able to order joins in a way where LEFT is always used

Filtering Results

  • When using only INNER JOIN, there is no need for a WHERE clause. Include your filters that would otherwise be in a WHERE clause in the highest join you can (based on what tables you want to filter)

    • This will help the query perform more efficiently due to needing to compare fewer rows as part of the JOIN statements

  • Avoid use of OR clauses, as they are generally inefficient. Favor using IN as an alternate

❌Avoid This

SELECT g.name AS obj_name,
m.name AS kr_name,
ms.createdat AS update_date
FROM gtmhubgoals g
INNER JOIN
gtmhubmetrics m
ON g.id = m.goalid
INNER JOIN
gtmhubmetricsnapshots ms
ON ms.metricid = m.id
WHERE
g.name = 'An Objective'

✔️Do This

SELECT g.name AS obj_name,
m.name AS kr_name,
ms.createdat AS update_date
FROM gtmhubgoals g
INNER JOIN
gtmhubmetrics m
ON g.id = m.goalid
AND g.name = 'An Objective'
INNER JOIN
gtmhubmetricsnapshots ms
ON ms.metricid = m.id

Result Limit

  • If there is any possibility of retrieving more than 5000 rows, include LIMIT 5000 at the end of the query

    • If you are using aggregations that get you down to a single row, you don’t need to include this


See Also

Code Best Practices: Insight HTML/CSS

Code Best Practices: Insight JavaScript

Did this answer your question?