Enter SQL Text
These are the rules being applied (you can update)
General formatting Keep all comments Use all caps for keywords such as SELECT, WHERE, GROUP BY etc. and also for function call like SUM(), COUNT(), TIMESTAMP() etc. As a general rule of thumb, if it changes colour when you type it, it should be all caps Make use of whitespace. Leave at least one blank line after each complete statement e.g. a temp table or function, a CTE, etc. Within a script, be consistent with the spacing i.e. if you choose to leave two lines after a statement, always leave two lines For long or complex queries, include a comment block at the top of the file to explain what it does, how it does it, and why we need it Define all variables together at the beginning of the script If variables get set at the start, set them all together, leaving a blank line between their declare statements e.g. Subqueries should follow all of the formatting rules of a regular query, but everything should be indented from the parent FROM statement e.g. If a comment is relevant to a block of code, put the comment directly above the line that the code block starts on Split long comments across multiple lines so that they can display on most screens without scrolling sideways Field names should be in lowercase, and should use snake case (underscores instead of spaces) where there are multiple words e.g. transaction_id, list_value, product_sku Field names should not be SQL keywords e.g. date. In some cases we ingest tables where these field names are already set, in these cases it is best to wrap the field names in backticks when referenced in the query e.g. Use AS when aliasing fields and tables. Alias tables when their names are particularly long e.g. 20+ characters. Naming CTEs, subqueries, temp tables Table names/subquery aliases should be lower case, and should use snake case They should be descriptive, explicit names Avoid using things like t, a, b, x, cte, etc. They should be reasonably short and succinct, where possible Indent selected fields so that they all align Indent selected fields so that they are a level of indentation 'deeper' than the SELECT keyword Align the FROM keyword with the relevant SELECT keyword, this way all of the selected fields will sit ‘within’ the two keywords For any additional tables/data sources being selected from in the same FROM statement, align with the first table in the statement Align the JOIN keyword to the FROM keyword for the table it is joining to Indent the join conditions one level deeper than the JOIN keyword Align additional join condition with the first condition Any additional joins should be aligned with the FROM statement Specify the type of join e.g. INNER JOIN, instead of just JOIN Filter clauses (WHERE, HAVING, QUALIFY, etc.) Align the filter keyword to the relevant FROM keyword Put the first boolean condition either on the same line as the filter keyword or on the line below the filter keyword, indented one level deeper Indent any additional conditions one level deeper than the filter keyword Align the GROUP BY keywords to the relevant FROM keyword Put the first field in the clause either on the same line as the GROUP BY keywords or on the line below, indented one level deeper Indent any additional conditions one level deeper than the filter keyword, or aligned with the first field Specify fields by name, if necessary using the table ref Do not specify by reference e.g. GROUP BY 1, 2, 3 If there are lots of fields to aggregate by, instead of listing them all individually you could opt to use GROUP BY ALL, bearing in mind the limitation of that function (detailed here) Align the CASE keyword with the rest of the fields in the SELECT statement (if in a SELECT statement) Align WHEN and ELSE keywords together For long WHEN conditions: Put any subsequent boolean conditions on a new line, one step deeper than the parent WHEN keyword If there are multipart expressions wrapped in parentheses, try to keep these on the same line. If they are too long for one line, align each line to the opening bracket Put the THEN keyword on a new line, indented one level deeper than the parent WHEN keyword Align the END keyword with the CASE keyword Align all subsequent IF, ELSE and END IF keywords to the parent IF keyword Use a comment(s) directly above the IF/ELSE keywords to clarify what each clause captures
Submit