SQL Video editing

User Sub Reg Aggregates

User Sub Reg Aggregates model for video editing.

source-preserved

Blocks

dbt SQL LKML LookML

user_sub_reg_aggregates.sql · 96 lines

inferred

-- models/user_sub_reg_aggregates.sql
{{ config(materialized='table') }}

SELECT 'brand new subs' AS type
  , transactions.id AS sub_id
  , NULL AS order_id
  , transactions.user_id AS user_id
  , transactions.created_at AS created
  , 0 AS total_cost
FROM transactions
LEFT JOIN users
ON users.id = transactions.user_id
WHERE DATEDIFF(mm, transactions.processed_at, users.created_at) = 0
AND transactions.processed_at >= <% parameter start_date %>
AND transactions.rate_id IS NOT NULL

UNION ALL

SELECT 'new subs' AS type
  , id AS sub_id
  , NULL AS order_id
  , user_id AS user_id
  , created_at AS created
  , 0 AS total_cost
FROM (SELECT id
        , user_id
        , created_at
        , rate_id
        , processed_at
        , RANK() OVER(PARTITION BY user_id ORDER BY created_at) AS order_sequence
      FROM transactions
      WHERE transactions.rate_id IS NOT NULL) AS transactions
WHERE order_sequence = 1
AND transactions.processed_at >= <% parameter start_date %>

UNION ALL

SELECT 'renewal subs' AS type
  , id AS sub_id
  , NULL AS order_id
  , user_id AS user_id
  , created_at AS created
  , 0 AS total_cost
FROM (SELECT id
        , user_id
        , created_at
        , rate_id
        , processed_at
        , RANK() OVER(PARTITION BY user_id ORDER BY created_at) AS order_sequence
      FROM transactions
      WHERE transactions.rate_id IS NOT NULL) AS transactions
WHERE order_sequence > 1
AND transactions.processed_at >= <% parameter start_date %>

UNION ALL

SELECT 'new bookings' AS type
  , NULL AS sub_id
  , id AS order_id
  , user_id AS user_id
  , created_at AS created
  , ISNULL(exchanged_total_cost, total_cost) AS total_cost
FROM (SELECT id
        , user_id
        , created_at
        , rate_id
        , processed_at
        , exchanged_total_cost
        , total_cost
        , RANK() OVER(PARTITION BY user_id ORDER BY created_at) AS order_sequence
      FROM transactions
      WHERE transactions.rate_id IS NOT NULL) AS transactions
WHERE order_sequence > 1
AND transactions.processed_at >= <% parameter start_date %>

UNION ALL

SELECT 'registration' AS type
  , NULL AS sub_id
  , NULL AS order_id
  , id AS user_id
  , created_at AS created
  , NULL AS total_cost
FROM users

# models/schema.yml
version: 2

models:
  - name: user_sub_reg_aggregates
    columns:
      - name: type
      - name: subscription_id
      - name: order_id
      - name: user_id
      - name: total_cost

Assumptions

materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

user_sub_reg_aggregates.sql · 81 lines

source-preserved validation: not checked

SELECT 'brand new subs' AS type
  , transactions.id AS sub_id
  , NULL AS order_id
  , transactions.user_id AS user_id
  , transactions.created_at AS created
  , 0 AS total_cost
FROM transactions
LEFT JOIN users
ON users.id = transactions.user_id
WHERE DATEDIFF(mm, transactions.processed_at, users.created_at) = 0
AND transactions.processed_at >= <% parameter start_date %>
AND transactions.rate_id IS NOT NULL

UNION ALL

SELECT 'new subs' AS type
  , id AS sub_id
  , NULL AS order_id
  , user_id AS user_id
  , created_at AS created
  , 0 AS total_cost
FROM (SELECT id
        , user_id
        , created_at
        , rate_id
        , processed_at
        , RANK() OVER(PARTITION BY user_id ORDER BY created_at) AS order_sequence
      FROM transactions
      WHERE transactions.rate_id IS NOT NULL) AS transactions
WHERE order_sequence = 1
AND transactions.processed_at >= <% parameter start_date %>

UNION ALL

SELECT 'renewal subs' AS type
  , id AS sub_id
  , NULL AS order_id
  , user_id AS user_id
  , created_at AS created
  , 0 AS total_cost
FROM (SELECT id
        , user_id
        , created_at
        , rate_id
        , processed_at
        , RANK() OVER(PARTITION BY user_id ORDER BY created_at) AS order_sequence
      FROM transactions
      WHERE transactions.rate_id IS NOT NULL) AS transactions
WHERE order_sequence > 1
AND transactions.processed_at >= <% parameter start_date %>

UNION ALL

SELECT 'new bookings' AS type
  , NULL AS sub_id
  , id AS order_id
  , user_id AS user_id
  , created_at AS created
  , ISNULL(exchanged_total_cost, total_cost) AS total_cost
FROM (SELECT id
        , user_id
        , created_at
        , rate_id
        , processed_at
        , exchanged_total_cost
        , total_cost
        , RANK() OVER(PARTITION BY user_id ORDER BY created_at) AS order_sequence
      FROM transactions
      WHERE transactions.rate_id IS NOT NULL) AS transactions
WHERE order_sequence > 1
AND transactions.processed_at >= <% parameter start_date %>

UNION ALL

SELECT 'registration' AS type
  , NULL AS sub_id
  , NULL AS order_id
  , id AS user_id
  , created_at AS created
  , NULL AS total_cost
FROM users

user_sub_reg_aggregates.view.lkml · 195 lines

inferred

view: user_sub_reg_aggregates {
  derived_table: {
    sql: SELECT 'brand new subs' AS type
  , transactions.id AS sub_id
  , NULL AS order_id
  , transactions.user_id AS user_id
  , transactions.created_at AS created
  , 0 AS total_cost
FROM transactions
LEFT JOIN users
ON users.id = transactions.user_id
WHERE DATEDIFF(mm, transactions.processed_at, users.created_at) = 0
AND transactions.processed_at >= <% parameter start_date %>
AND transactions.rate_id IS NOT NULL

UNION ALL

SELECT 'new subs' AS type
  , id AS sub_id
  , NULL AS order_id
  , user_id AS user_id
  , created_at AS created
  , 0 AS total_cost
FROM (SELECT id
        , user_id
        , created_at
        , rate_id
        , processed_at
        , RANK() OVER(PARTITION BY user_id ORDER BY created_at) AS order_sequence
      FROM transactions
      WHERE transactions.rate_id IS NOT NULL) AS transactions
WHERE order_sequence = 1
AND transactions.processed_at >= <% parameter start_date %>

UNION ALL

SELECT 'renewal subs' AS type
  , id AS sub_id
  , NULL AS order_id
  , user_id AS user_id
  , created_at AS created
  , 0 AS total_cost
FROM (SELECT id
        , user_id
        , created_at
        , rate_id
        , processed_at
        , RANK() OVER(PARTITION BY user_id ORDER BY created_at) AS order_sequence
      FROM transactions
      WHERE transactions.rate_id IS NOT NULL) AS transactions
WHERE order_sequence > 1
AND transactions.processed_at >= <% parameter start_date %>

UNION ALL

SELECT 'new bookings' AS type
  , NULL AS sub_id
  , id AS order_id
  , user_id AS user_id
  , created_at AS created
  , ISNULL(exchanged_total_cost, total_cost) AS total_cost
FROM (SELECT id
        , user_id
        , created_at
        , rate_id
        , processed_at
        , exchanged_total_cost
        , total_cost
        , RANK() OVER(PARTITION BY user_id ORDER BY created_at) AS order_sequence
      FROM transactions
      WHERE transactions.rate_id IS NOT NULL) AS transactions
WHERE order_sequence > 1
AND transactions.processed_at >= <% parameter start_date %>

UNION ALL

SELECT 'registration' AS type
  , NULL AS sub_id
  , NULL AS order_id
  , id AS user_id
  , created_at AS created
  , NULL AS total_cost
FROM users ;;
    persist_for: "2 hours"
  }

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

  dimension: subscription_id {
    type: int
    hidden: yes
    sql: ${TABLE}.subscription_id ;;
  }

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

  dimension: user_id {
    type: int
    hidden: yes
    sql: ${TABLE}.user_id ;;
  }

  dimension: total_cost {
    type: number
    sql: ${TABLE}.total_cost/100 ;;
  }

  dimension_group: created {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.created ;;
  }

  measure: registrations {
    type: count
    filters: [type: "registration"]
  }

  measure: percent_change_registrations {
    type: percent_of_previous
    sql: ${registrations} ;;
  }

  measure: bookings_count {
    type: count
    filters: [type: "new bookings"]
  }

  measure: percent_change_bookings {
    type: percent_of_previous
    sql: ${bookings_count} ;;
  }

  measure: brand_new_subs {
    type: count
    filters: [type: "brand new subs"]
  }

  measure: percent_change_brand_new_subs {
    type: percent_of_previous
    sql: ${brand_new_subs} ;;
  }

  measure: new_subs {
    type: count
    filters: [type: "new subs"]
  }

  measure: percent_change_new_subs {
    type: percent_of_previous
    sql: ${new_subs} ;;
  }

  measure: renewal_subs {
    type: count
    filters: [type: "renewal subs"]
  }

  measure: percent_change_renewal_subs {
    type: percent_of_previous
    sql: ${renewal_subs} ;;
  }

  measure: total_subs {
    type: number
    sql: ${brand_new_subs} + ${new_subs} + ${renewal_subs} ;;
  }

  measure: percent_change_total_subs {
    type: percent_of_previous
    sql: ${total_subs} ;;
  }

  measure: booking_value {
    type: sum
    sql: ${total_cost} ;;
  }

  measure: percent_change_booking_value {
    type: percent_of_previous
    sql: ${booking_value} ;;
  }

  measure: aov {
    label: "USER AGGREGATES AOV"
    type: number
    sql: ${booking_value} / NULLIF(${bookings_count}, 0) ;;
  }
}

user_sub_reg_aggregates.view.lookml · 161 lines

source-preserved

- view: user_sub_reg_aggregates
  derived_table:
    sql: SELECT 'brand new subs' AS type
  , transactions.id AS sub_id
  , NULL AS order_id
  , transactions.user_id AS user_id
  , transactions.created_at AS created
  , 0 AS total_cost
FROM transactions
LEFT JOIN users
ON users.id = transactions.user_id
WHERE DATEDIFF(mm, transactions.processed_at, users.created_at) = 0
AND transactions.processed_at >= <% parameter start_date %>
AND transactions.rate_id IS NOT NULL

UNION ALL

SELECT 'new subs' AS type
  , id AS sub_id
  , NULL AS order_id
  , user_id AS user_id
  , created_at AS created
  , 0 AS total_cost
FROM (SELECT id
        , user_id
        , created_at
        , rate_id
        , processed_at
        , RANK() OVER(PARTITION BY user_id ORDER BY created_at) AS order_sequence
      FROM transactions
      WHERE transactions.rate_id IS NOT NULL) AS transactions
WHERE order_sequence = 1
AND transactions.processed_at >= <% parameter start_date %>

UNION ALL

SELECT 'renewal subs' AS type
  , id AS sub_id
  , NULL AS order_id
  , user_id AS user_id
  , created_at AS created
  , 0 AS total_cost
FROM (SELECT id
        , user_id
        , created_at
        , rate_id
        , processed_at
        , RANK() OVER(PARTITION BY user_id ORDER BY created_at) AS order_sequence
      FROM transactions
      WHERE transactions.rate_id IS NOT NULL) AS transactions
WHERE order_sequence > 1
AND transactions.processed_at >= <% parameter start_date %>

UNION ALL

SELECT 'new bookings' AS type
  , NULL AS sub_id
  , id AS order_id
  , user_id AS user_id
  , created_at AS created
  , ISNULL(exchanged_total_cost, total_cost) AS total_cost
FROM (SELECT id
        , user_id
        , created_at
        , rate_id
        , processed_at
        , exchanged_total_cost
        , total_cost
        , RANK() OVER(PARTITION BY user_id ORDER BY created_at) AS order_sequence
      FROM transactions
      WHERE transactions.rate_id IS NOT NULL) AS transactions
WHERE order_sequence > 1
AND transactions.processed_at >= <% parameter start_date %>

UNION ALL

SELECT 'registration' AS type
  , NULL AS sub_id
  , NULL AS order_id
  , id AS user_id
  , created_at AS created
  , NULL AS total_cost
FROM users
    persist_for: 2 hours
  fields:
  - dimension: type
    sql: ${TABLE}.type
  - dimension: subscription_id
    type: int
    hidden: true
    sql: ${TABLE}.subscription_id
  - dimension: order_id
    type: int
    hidden: true
    sql: ${TABLE}.order_id
  - dimension: user_id
    type: int
    hidden: true
    sql: ${TABLE}.user_id
  - dimension: total_cost
    type: number
    sql: ${TABLE}.total_cost/100
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created
  - measure: registrations
    type: count
    filters:
    - type: registration
  - measure: percent_change_registrations
    type: percent_of_previous
    sql: ${registrations}
  - measure: bookings_count
    type: count
    filters:
    - type: new bookings
  - measure: percent_change_bookings
    type: percent_of_previous
    sql: ${bookings_count}
  - measure: brand_new_subs
    type: count
    filters:
    - type: brand new subs
  - measure: percent_change_brand_new_subs
    type: percent_of_previous
    sql: ${brand_new_subs}
  - measure: new_subs
    type: count
    filters:
    - type: new subs
  - measure: percent_change_new_subs
    type: percent_of_previous
    sql: ${new_subs}
  - measure: renewal_subs
    type: count
    filters:
    - type: renewal subs
  - measure: percent_change_renewal_subs
    type: percent_of_previous
    sql: ${renewal_subs}
  - measure: total_subs
    type: number
    sql: ${brand_new_subs} + ${new_subs} + ${renewal_subs}
  - measure: percent_change_total_subs
    type: percent_of_previous
    sql: ${total_subs}
  - measure: booking_value
    type: sum
    sql: ${total_cost}
  - measure: percent_change_booking_value
    type: percent_of_previous
    sql: ${booking_value}
  - measure: aov
    type: number
    label: USER AGGREGATES AOV
    sql: ${booking_value} / NULLIF(${bookings_count}, 0)

user_sub_reg_aggregates.sql
user_sub_reg_aggregates.sql
user_sub_reg_aggregates.view.lkml
user_sub_reg_aggregates.view.lookml

User Sub Reg Aggregates

Blocks

Assumptions

Contents