Format the SQL query provided using this format below:

SQL Formatting Rules

General Structure

• •Use lowercase for all SQL keywords (select, from, where, join, etc.)
• •Use consistent 2-space indentation for nested structures
• •Place each major clause on its own line

SELECT Statement Formatting

Column List

• •First column starts immediately after select or select distinct
• •Subsequent columns:
  • •Comma at the beginning of the line
  • •Column name starts exactly 7 characters after the comma
  • •Align all column names vertically

Example:

select distinct
       column1
,      column2
,      column3

Column Aliases

• •Use as for all aliases
• •Align aliases vertically at the end of expressions
• •For long expressions, place as alias_name at the end with appropriate spacing

Example:

select distinct
       venue_uuid                                    as venue_uuid
,      organization_uuid                             as uuid
,      first_value(asset_id) over w_asset_last       as asset_id

FROM and JOIN Clauses

• •from starts at the beginning of the line
• •Table name follows with 3 spaces after from
• •For joins:
  • •Join type indented with appropriate spacing
  • •on conditions aligned under the join
  • •Multiple join conditions connected with and at the start of new lines

Example:

from   base b
       join ( select venue_uuid
              ,      cabinet_serial_number
              from   another_table
            ) p
            on p.venue_uuid            = b.venue_uuid
          and p.cabinet_serial_number  = b.cabinet_serial_number

WHERE Clause

• •where at the beginning of the line
• •First condition on the same line after 2 spaces
• •Additional conditions:
  • •and/or at the start of the line with 2 spaces
  • •Condition follows after 2 more spaces
  • •Align comparison operators vertically when possible

Example:

where  organization_uuid    in ('9ce76aab-7ce0-4012-8ebd-ee6ad6dd7462')
  and  current_asset_status  = 'Active'
  and  nbr_of_months_ago < 3

Window Functions

• •Define window aliases at the end of the query
• •Format window definition:
  • •window keyword at start of line
  • •Window name and as followed by opening parenthesis
  • •partition by and order by on separate lines with proper indentation
  • •Closing parenthesis aligned with window

Example:

window w_asset_last as ( partition by venue_uuid
                                   ,  asset_id
                             order by reporting_month_yyyymm desc
                       )

CTEs (Common Table Expressions)

• •with at the beginning
• •CTE name followed by as (
• •Opening parenthesis on the same line
• •CTE body indented
• •Closing parenthesis at the beginning of the line
• •Comma before next CTE (if any)

Example:

with base as (
select distinct
       column1
,      column2
from   table_name
),
trending as (
  select b.*
  ,      additional_column
  from   base b
)

CASE Statements

• •case at appropriate indentation level
• •when conditions indented 2 spaces from case
• •then on the same line as when
• •else at the same indentation as when
• •end at the same indentation as case
• •Alias after end when used in SELECT

Example:

,      case
         when best_new_installs <= 3
         then 1
         when worst_new_installs <= 3
         then 1
         end as ind_trending

Additional Rules

• •Use single quotes for string literals
• •Escape single quotes in strings with \'
• •Place order by at the end of queries
• •Group related columns together
• •Add comments for complex logic or CTEs
• •Maintain consistent spacing for readability
• •Align mathematical operators in complex calculations

Subqueries

• •Indent subquery content by 2 spaces from the opening parenthesis
• •Format subqueries following the same rules as main queries
• •Place closing parenthesis at the same indentation as the opening statement