Overview

This article contains the Quantive Results 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 quantiveresultsgoals g
    INNER JOIN
    quantiveresultsmetrics m
        ON g.id = m.goalid
    INNER JOIN
    quantiveresultsmetricsnapshots 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 quantiveresultsgoals g
    INNER JOIN
    quantiveresultsmetrics m
        ON g.id = m.goalid
    INNER JOIN
    quantiveresultsmetricsnapshots 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 quantiveresultsgoals g
    INNER JOIN
    quantiveresultsmetrics m
        ON g.id = m.goalid
    INNER JOIN
    quantiveresultsmetricsnapshots 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 quantiveresultsgoals g
    INNER JOIN
    quantiveresultsmetrics m
        ON g.id = m.goalid
            AND g.name = 'An Objective'
    INNER JOIN
    quantiveresultsmetricsnapshots 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

Overview

General Points

Table Aliases

Column Aliases

Joins

Filtering Results

Result Limit

See Also