First Order Ratings

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

SELECT
distinct oratings.user_id
, oratings.rating
, MIN(o.created_at) AS created_at_min
, oratings.order_id
          FROM order_ratings AS oratings
          INNER JOIN orders AS o ON o.id = oratings.order_id
          INNER JOIN orders AS so ON oratings.order_id = so.order_id
          WHERE NOT EXISTS
            (SELECT 1 FROM orders AS o
            WHERE o.user_id = oratings.user_id and o.id < oratings.order_id)
          AND EXISTS (SELECT 1 FROM orders so WHERE so.order_id = oratings.id and so.status = 6)
          GROUP BY oratings.user_id

# models/schema.yml
version: 2

models:
  - name: first_order_ratings
    columns:
      - name: total_buyer_pool_test
      - name: user_id
        tests:
          - unique
          - not_null
      - name: rating
      - name: order_id

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
distinct oratings.user_id
, oratings.rating
, MIN(o.created_at) AS created_at_min
, oratings.order_id
          FROM order_ratings AS oratings
          INNER JOIN orders AS o ON o.id = oratings.order_id
          INNER JOIN orders AS so ON oratings.order_id = so.order_id
          WHERE NOT EXISTS
            (SELECT 1 FROM orders AS o
            WHERE o.user_id = oratings.user_id and o.id < oratings.order_id)
          AND EXISTS (SELECT 1 FROM orders so WHERE so.order_id = oratings.id and so.status = 6)
          GROUP BY oratings.user_id

view: first_order_ratings {
  derived_table: {
    sql: SELECT
distinct oratings.user_id
, oratings.rating
, MIN(o.created_at) AS created_at_min
, oratings.order_id
          FROM order_ratings AS oratings
          INNER JOIN orders AS o ON o.id = oratings.order_id
          INNER JOIN orders AS so ON oratings.order_id = so.order_id
          WHERE NOT EXISTS
            (SELECT 1 FROM orders AS o
            WHERE o.user_id = oratings.user_id and o.id < oratings.order_id)
          AND EXISTS (SELECT 1 FROM orders so WHERE so.order_id = oratings.id and so.status = 6)
          GROUP BY oratings.user_id ;;
  }

  dimension: total_buyer_pool_test {
    type: number
    sql: (
    SELECT {% parameter state_2 %}UNT(DISTINCT o.user_id)
      FROM order_ratings AS oratings
           INNER JOIN orders AS o ON o.id = oratings.order_id
           INNER JOIN orders AS so ON oratings.order_id = so.order_id
           WHERE NOT EXISTS
             (SELECT 1 FROM orders AS o
             WHERE o.user_id = oratings.user_id and o.id < oratings.order_id)
           AND EXISTS (SELECT 1 FROM orders so WHERE so.order_id = oratings.id and so.status = 6)
 ) ;;
  }

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

  dimension: rating {
    label: "First Rating"
    type: number
    sql: ${TABLE}.rating ;;
  }

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

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

  measure: count {
    type: count
    drill_fields: [detail*]
  }
}

- view: first_order_ratings
  derived_table:
    sql: SELECT
distinct oratings.user_id
, oratings.rating
, MIN(o.created_at) AS created_at_min
, oratings.order_id
          FROM order_ratings AS oratings
          INNER JOIN orders AS o ON o.id = oratings.order_id
          INNER JOIN orders AS so ON oratings.order_id = so.order_id
          WHERE NOT EXISTS
            (SELECT 1 FROM orders AS o
            WHERE o.user_id = oratings.user_id and o.id < oratings.order_id)
          AND EXISTS (SELECT 1 FROM orders so WHERE so.order_id = oratings.id and so.status = 6)
          GROUP BY oratings.user_id
  fields:
  - dimension: total_buyer_pool_test
    type: number
    sql: (
    SELECT {% parameter state_2 %}UNT(DISTINCT o.user_id)
      FROM order_ratings AS oratings
           INNER JOIN orders AS o ON o.id = oratings.order_id
           INNER JOIN orders AS so ON oratings.order_id = so.order_id
           WHERE NOT EXISTS
             (SELECT 1 FROM orders AS o
             WHERE o.user_id = oratings.user_id and o.id < oratings.order_id)
           AND EXISTS (SELECT 1 FROM orders so WHERE so.order_id = oratings.id and so.status = 6)
 )
  - dimension: user_id
    type: number
    primary_key: true
    sql: ${TABLE}.user_id
  - dimension: rating
    type: number
    label: First Rating
    sql: ${TABLE}.rating
  - dimension: order_id
    type: number
    sql: ${TABLE}.order_id
  - dimension_group: created_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_at_min
  - measure: count
    type: count
    drill_fields:
    - detail*