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.
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.
- explore: orders
- join: order_user_sequence
- view: orders
- dimension: id
- measure: 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
id AS order_id
, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at) as user_order_sequence_number
- dimension: order_id
- dimension: user_order_sequence_number