User Booking Facts

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

SELECT
  "user".id AS user_id
  , COUNT(*) AS lifetime_bookings
  , COUNT(DISTINCT TO_CHAR(bookings_booking.create_date, 'YYYY-MM')) AS distinct_months_with_booking
  , MIN(bookings_booking.create_date) AS first_booking
  , MAX(bookings_booking.create_date) AS latest_booking
  , DATE_PART('day', CURRENT_DATE - MAX(bookings_booking.create_date)) AS days_since_booking
  , DATE_PART('day', CURRENT_DATE - MIN(bookings_booking.create_date)) AS days_since_first_booking
FROM bookings_booking
LEFT JOIN users_userprofile AS userprofile ON bookings_booking.profile_id = userprofile.id
LEFT JOIN auth_user AS "user" ON userprofile.user_id = "user".id
WHERE bookings_booking.status = 10
GROUP BY 1

# models/schema.yml
version: 2

models:
  - name: user_booking_facts
    columns:
      - name: user_id
        tests:
          - unique
          - not_null
      - name: lifetime_bookings
      - name: is_repeat_booker
      - name: lifetime_bookings_tier
      - name: number_of_distinct_months_with_booking
      - name: days_since_last_booking
      - name: days_since_first_booking
      - name: months_since_first_booking
      - name: days_since_first_booking_tier

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
  "user".id AS user_id
  , COUNT(*) AS lifetime_bookings
  , COUNT(DISTINCT TO_CHAR(bookings_booking.create_date, 'YYYY-MM')) AS distinct_months_with_booking
  , MIN(bookings_booking.create_date) AS first_booking
  , MAX(bookings_booking.create_date) AS latest_booking
  , DATE_PART('day', CURRENT_DATE - MAX(bookings_booking.create_date)) AS days_since_booking
  , DATE_PART('day', CURRENT_DATE - MIN(bookings_booking.create_date)) AS days_since_first_booking
FROM bookings_booking
LEFT JOIN users_userprofile AS userprofile ON bookings_booking.profile_id = userprofile.id
LEFT JOIN auth_user AS "user" ON userprofile.user_id = "user".id
WHERE bookings_booking.status = 10
GROUP BY 1

view: user_booking_facts {
  derived_table: {
    sql: SELECT
  "user".id AS user_id
  , COUNT(*) AS lifetime_bookings
  , COUNT(DISTINCT TO_CHAR(bookings_booking.create_date, 'YYYY-MM')) AS distinct_months_with_booking
  , MIN(bookings_booking.create_date) AS first_booking
  , MAX(bookings_booking.create_date) AS latest_booking
  , DATE_PART('day', CURRENT_DATE - MAX(bookings_booking.create_date)) AS days_since_booking
  , DATE_PART('day', CURRENT_DATE - MIN(bookings_booking.create_date)) AS days_since_first_booking
FROM bookings_booking
LEFT JOIN users_userprofile AS userprofile ON bookings_booking.profile_id = userprofile.id
LEFT JOIN auth_user AS "user" ON userprofile.user_id = "user".id
WHERE bookings_booking.status = 10
GROUP BY 1 ;;
  }

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

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

  dimension: is_repeat_booker {
    type: yesno
    sql: ${lifetime_bookings} > 1 ;;
  }

  dimension: lifetime_bookings_tier {
    type: tier
    sql: ${lifetime_bookings} ;;
  }

  dimension: number_of_distinct_months_with_booking {
    type: int
    sql: ${TABLE}.distinct_months_with_booking ;;
  }

  dimension: days_since_last_booking {
    type: int
    sql: ${TABLE}.days_since_booking ;;
  }

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

  dimension: months_since_first_booking {
    type: int
    sql: FLOOR(${days_since_first_booking} / 30) ;;
  }

  dimension: days_since_first_booking_tier {
    type: tier
    sql: ${days_since_first_booking} ;;
  }

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

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

  measure: average_lifetime_bookings {
    type: avg
    sql: ${lifetime_bookings} ;;
  }
}

- view: user_booking_facts
  derived_table:
    sql: SELECT
  "user".id AS user_id
  , COUNT(*) AS lifetime_bookings
  , COUNT(DISTINCT TO_CHAR(bookings_booking.create_date, 'YYYY-MM')) AS distinct_months_with_booking
  , MIN(bookings_booking.create_date) AS first_booking
  , MAX(bookings_booking.create_date) AS latest_booking
  , DATE_PART('day', CURRENT_DATE - MAX(bookings_booking.create_date)) AS days_since_booking
  , DATE_PART('day', CURRENT_DATE - MIN(bookings_booking.create_date)) AS days_since_first_booking
FROM bookings_booking
LEFT JOIN users_userprofile AS userprofile ON bookings_booking.profile_id = userprofile.id
LEFT JOIN auth_user AS "user" ON userprofile.user_id = "user".id
WHERE bookings_booking.status = 10
GROUP BY 1
  fields:
  - dimension: user_id
    type: int
    primary_key: true
    sql: ${TABLE}.user_id
  - dimension: lifetime_bookings
    type: number
    sql: ${TABLE}.lifetime_bookings
  - dimension: is_repeat_booker
    type: yesno
    sql: ${lifetime_bookings} > 1
  - dimension: lifetime_bookings_tier
    type: tier
    sql: ${lifetime_bookings}
  - dimension: number_of_distinct_months_with_booking
    type: int
    sql: ${TABLE}.distinct_months_with_booking
  - dimension: days_since_last_booking
    type: int
    sql: ${TABLE}.days_since_booking
  - dimension: days_since_first_booking
    type: int
    sql: ${TABLE}.days_since_first_booking
  - dimension: months_since_first_booking
    type: int
    sql: FLOOR(${days_since_first_booking} / 30)
  - dimension: days_since_first_booking_tier
    type: tier
    sql: ${days_since_first_booking}
  - dimension_group: first_booking
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.first_booking
  - dimension_group: latest_booking
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.latest_booking
  - measure: average_lifetime_bookings
    type: avg
    sql: ${lifetime_bookings}