Sequencing Transactions With Window Functions

[lloydtabb]

Sequencing Transactions is a very common analytical pattern. In the example below, we will compute a sequence number for each order indicating if the order was the customer’s first, second, third, or Nth order.

This requires the use of persistent derived tables and window functions. MySQL databases do not have window functions, and thus should use a correlated subquery.

How it’s done.

A persistent derived table is created with two columns.

order_id, sequence_num

For each order in the orders table, a row is created in the order_user_sequence table. The table is then indexed and joined into orders.

Example model:

- explore: orders
  joins:
  - join: order_user_sequence
    foreign_key: id

- view: orders
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id

  - measure: count
    type: count
    detail: [id, created_time]

# Compute the order's sequence over the users lifetime.  Is this the first, second, third, etc.

- view: order_user_sequence
  derived_table:
    sql: |
      SELECT
        id AS order_id
        , ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at) as user_order_sequence_number
      FROM orders
    
  fields:
  - dimension: order_id
    primary_key: true
    sql: ${TABLE}.order_id

  - dimension: user_order_sequence_number
    type: int
    sql: ${TABLE}.user_order_sequence_number

Orders by Order Sequence Number, the last 10 days.

Explore Data