Session Facts

-- models/session_facts.sql
{{ config(materialized='view') }}

SELECT
s.user_id AS user_id
, s.start AS start
, (CASE s.user_id
  WHEN @curUser
  THEN @curRow := @curRow + 1
  ELSE @curRow := 1 AND @curUser := s.user_id END) AS sequence_number
, s.credit_amount_usd AS credit_amount_usd
FROM
  (SELECT
    s1.user_id AS user_id
    , s1.start AS start
    , s1.end AS end
    , SUM(c.currency_amount * c.payout_foreign_exchange_rate) AS credit_amount_usd
  FROM public_sessions AS s1
  JOIN public_credits AS c
  ON s1.user_id = c.user_id
  AND s1.end > c.time AND s1.start < c.time
  GROUP BY 1,2,3
  ORDER BY s1.user_id, s1.start ASC) AS s,
(SELECT @curRow := 0, @curUser := '') r
GROUP BY 1,2,3,4

# models/schema.yml
version: 2

models:
  - name: session_facts
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: sequence_number
      - name: has_credits
      - name: credit_amount_usd
      - name: days_since_signup

Assumptions

• materialized='view' is the default; no persist hint found.
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT
s.user_id AS user_id
, s.start AS start
, (CASE s.user_id
  WHEN @curUser
  THEN @curRow := @curRow + 1
  ELSE @curRow := 1 AND @curUser := s.user_id END) AS sequence_number
, s.credit_amount_usd AS credit_amount_usd
FROM
  (SELECT
    s1.user_id AS user_id
    , s1.start AS start
    , s1.end AS end
    , SUM(c.currency_amount * c.payout_foreign_exchange_rate) AS credit_amount_usd
  FROM public_sessions AS s1
  JOIN public_credits AS c
  ON s1.user_id = c.user_id
  AND s1.end > c.time AND s1.start < c.time
  GROUP BY 1,2,3
  ORDER BY s1.user_id, s1.start ASC) AS s,
(SELECT @curRow := 0, @curUser := '') r
GROUP BY 1,2,3,4

view: session_facts {
  derived_table: {
    sql: SELECT
s.user_id AS user_id
, s.start AS start
, (CASE s.user_id
  WHEN @curUser
  THEN @curRow := @curRow + 1
  ELSE @curRow := 1 AND @curUser := s.user_id END) AS sequence_number
, s.credit_amount_usd AS credit_amount_usd
FROM
  (SELECT
    s1.user_id AS user_id
    , s1.start AS start
    , s1.end AS end
    , SUM(c.currency_amount * c.payout_foreign_exchange_rate) AS credit_amount_usd
  FROM public_sessions AS s1
  JOIN public_credits AS c
  ON s1.user_id = c.user_id
  AND s1.end > c.time AND s1.start < c.time
  GROUP BY 1,2,3
  ORDER BY s1.user_id, s1.start ASC) AS s,
(SELECT @curRow := 0, @curUser := '') r
GROUP BY 1,2,3,4 ;;
  }

  dimension: id {
    primary_key: yes
    sql: (${TABLE}.user_id * 1000000) + ${TABLE}.sequence_number ;;
  }

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

  dimension: has_credits {
    type: yesno
    sql: ${credit_amount_usd} > 0 ;;
  }

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

  dimension: days_since_signup {
    type: number
    sql: CURDATE() - ${users.account_creation_date} ;;
  }

  dimension_group: session_start {
    type: time
    timeframes: [time, date]
    sql: ${TABLE}.start ;;
  }

  measure: total_credit_amount_usd {
    type: sum
    sql: ${credit_amount_usd} ;;
  }

  measure: average_credit_amount_usd {
    type: avg
    sql: ${credit_amount_usd} ;;
  }

  measure: session_count {
    type: count
    sql: ${id} ;;
  }

  measure: sessions_with_purchases_count {
    type: count
    hidden: yes
    filters: [has_credits: "True"]
    sql: ${id} ;;
  }

  measure: sessions_with_purchases_percent {
    type: number
    sql: 100.0 * ${sessions_with_purchases_count} / ${session_count} ;;
  }
}

- view: session_facts
  derived_table:
    sql: SELECT
s.user_id AS user_id
, s.start AS start
, (CASE s.user_id
  WHEN @curUser
  THEN @curRow := @curRow + 1
  ELSE @curRow := 1 AND @curUser := s.user_id END) AS sequence_number
, s.credit_amount_usd AS credit_amount_usd
FROM
  (SELECT
    s1.user_id AS user_id
    , s1.start AS start
    , s1.end AS end
    , SUM(c.currency_amount * c.payout_foreign_exchange_rate) AS credit_amount_usd
  FROM public_sessions AS s1
  JOIN public_credits AS c
  ON s1.user_id = c.user_id
  AND s1.end > c.time AND s1.start < c.time
  GROUP BY 1,2,3
  ORDER BY s1.user_id, s1.start ASC) AS s,
(SELECT @curRow := 0, @curUser := '') r
GROUP BY 1,2,3,4
  fields:
  - dimension: id
    primary_key: true
    sql: (${TABLE}.user_id * 1000000) + ${TABLE}.sequence_number
  - dimension: sequence_number
    type: int
    sql: ${TABLE}.sequence_number
  - dimension: has_credits
    type: yesno
    sql: ${credit_amount_usd} > 0
  - dimension: credit_amount_usd
    type: number
    sql: ${TABLE}.credit_amount_usd
  - dimension: days_since_signup
    type: number
    sql: CURDATE() - ${users.account_creation_date}
  - dimension_group: session_start
    type: time
    timeframes:
    - time
    - date
    sql: ${TABLE}.start
  - measure: total_credit_amount_usd
    type: sum
    sql: ${credit_amount_usd}
  - measure: average_credit_amount_usd
    type: avg
    sql: ${credit_amount_usd}
  - measure: session_count
    type: count
    sql: ${id}
  - measure: sessions_with_purchases_count
    type: count
    filters:
    - has_credits: True
    sql: ${id}
  - measure: sessions_with_purchases_percent
    type: number
    sql: 100.0 * ${sessions_with_purchases_count} / ${session_count}