Window functions can be an easy and elegant way to add ranking, rolling averages, cumulative sums, and other powerful calculations to your queries. They can help answer some useful questions, such as:

• What is the most common first purchase?
• Which first purchases result in a lot of recurring purchases?
• Which purchases are the high-profit ones in a distribution of all purchases?
• What commonalities do transactions share within the same quartile?

Window functions let you perform aggregate calculations (like COUNT, SUM, and AVG, etc.) against a group of rows, then list the result next to all the rows in that group. They can also give you new types of calculations like RANK, LAG, and NTILE.

For an overview of how window functions work in SQL, check out this blog post written by one of our analysts.

In Looker, window functions are best implemented in derived tables, as shown in the examples below.

Example: Ranking by Order ID

view: order_window_facts {
 derived_table: {
 sql:
 SELECT
 order_id,
 RANK() OVER(PARTITION BY store ORDER BY order_value DESC)
 AS value_rank_by_store
 FROM
 order ;;
 }

dimension: order_id {
 type: number
 sql: ${TABLE}.order_id ;;
 primary_key: yes
 hidden: yes
 }

dimension: value_rank_by_store {
 type: number
 sql: ${TABLE}.value_rank_by_store ;;
 }
}

Example: Comparing Order Amount to the Previous Order Amount by that User

view: order_compare_facts {
 derived_table: {
 sql:
 SELECT
 order_id,
 order_amount,
 LAG(order_amount,1) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_order_amount
 FROM
 order ;;
 }

 dimension: order_id {
 type: number
 sql: ${TABLE}.order_id ;;
 primary_key: yes
 hidden: yes
 }

dimension: order_amount {
 type: number
 sql: ${TABLE}.order_amount;;
 }
 
dimension: previous_order_amount {
 type: number
 sql: ${TABLE}.prev_order_amount;;
 }
}