data.ml
dbt Marketplace automotive

Buyer Bid Facts

Buyer Bid Facts model for marketplace automotive.

inferred

Blocks

buyer_bid_facts.sql · 48 lines
inferred 
-- models/buyer_bid_facts.sql
{{ config(materialized='table') }}

SELECT
  fb.dim_bidder_user_id AS user_id
  , COUNT(*) AS lifetime_bids
  , SUM(fb.high_bids) AS lifetime_high_bids
  , SUM(
        CASE fb.high_bids
          WHEN 1
          THEN bid_amount
          ELSE 0
        END) AS lifetime_high_bid_amount
  , AVG(
        CASE fb.high_bids
          WHEN 1
          THEN bid_amount
          ELSE 0
        END) AS average_high_bid_amount
  , COUNT(DISTINCT fb.auc_id) AS lifetime_auctions
  , MIN(CONVERT(datetime, fb.bid_date, 120)) AS first_bid
  , MIN(
      CASE fb.high_bids
         WHEN 1 THEN CONVERT(datetime, fb.bid_date, 120)
         ELSE NULL
      END
    ) AS first_purchase
  , MAX(CONVERT(datetime, fb.bid_date, 120)) AS last_bid
FROM fact_bids AS fb
GROUP BY fb.dim_bidder_user_id

# models/schema.yml
version: 2

models:
  - name: buyer_bid_facts
    columns:
      - name: user_id
        tests:
          - unique
          - not_null
      - name: lifetime_bids
      - name: lifetime_high_bids
      - name: lifetime_high_bid_amount
      - name: average_high_bid_amount
      - name: lifetime_auctions
      - name: lifetime_share_high_bid
      - name: lifetime_winning_bids_tiered

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.
buyer_bid_facts.sql · 27 lines
source-preserved validation: not checked 
SELECT
  fb.dim_bidder_user_id AS user_id
  , COUNT(*) AS lifetime_bids
  , SUM(fb.high_bids) AS lifetime_high_bids
  , SUM(
        CASE fb.high_bids
          WHEN 1
          THEN bid_amount
          ELSE 0
        END) AS lifetime_high_bid_amount
  , AVG(
        CASE fb.high_bids
          WHEN 1
          THEN bid_amount
          ELSE 0
        END) AS average_high_bid_amount
  , COUNT(DISTINCT fb.auc_id) AS lifetime_auctions
  , MIN(CONVERT(datetime, fb.bid_date, 120)) AS first_bid
  , MIN(
      CASE fb.high_bids
         WHEN 1 THEN CONVERT(datetime, fb.bid_date, 120)
         ELSE NULL
      END
    ) AS first_purchase
  , MAX(CONVERT(datetime, fb.bid_date, 120)) AS last_bid
FROM fact_bids AS fb
GROUP BY fb.dim_bidder_user_id
buyer_bid_facts.view.lkml · 90 lines
inferred 
view: buyer_bid_facts {
  derived_table: {
    sql: SELECT
  fb.dim_bidder_user_id AS user_id
  , COUNT(*) AS lifetime_bids
  , SUM(fb.high_bids) AS lifetime_high_bids
  , SUM(
        CASE fb.high_bids
          WHEN 1
          THEN bid_amount
          ELSE 0
        END) AS lifetime_high_bid_amount
  , AVG(
        CASE fb.high_bids
          WHEN 1
          THEN bid_amount
          ELSE 0
        END) AS average_high_bid_amount
  , COUNT(DISTINCT fb.auc_id) AS lifetime_auctions
  , MIN(CONVERT(datetime, fb.bid_date, 120)) AS first_bid
  , MIN(
      CASE fb.high_bids
         WHEN 1 THEN CONVERT(datetime, fb.bid_date, 120)
         ELSE NULL
      END
    ) AS first_purchase
  , MAX(CONVERT(datetime, fb.bid_date, 120)) AS last_bid
FROM fact_bids AS fb
GROUP BY fb.dim_bidder_user_id ;;
    persist_for: "3 hours"
  }

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

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

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

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

  dimension: average_high_bid_amount {
    type: number
    sql: ROUND(${TABLE}.average_high_bid_amount,0) ;;
  }

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

  dimension: lifetime_share_high_bid {
    type: int
    sql: 100 * ${lifetime_high_bids}/${lifetime_auctions} ;;
  }

  dimension: lifetime_winning_bids_tiered {
    type: tier
    sql: ${lifetime_high_bids} ;;
  }

  dimension_group: first_bid {
    type: time
    timeframes: [time, date, week, month, year]
    sql: CONVERT(datetime, ${TABLE}.first_bid) ;;
  }

  dimension_group: last_bid {
    type: time
    timeframes: [time, date, week, month, year]
    sql: CONVERT(datetime, ${TABLE}.last_bid) ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }
}
buyer_bid_facts.view.lookml · 78 lines
source-preserved 
- view: buyer_bid_facts
  derived_table:
    sql: SELECT
  fb.dim_bidder_user_id AS user_id
  , COUNT(*) AS lifetime_bids
  , SUM(fb.high_bids) AS lifetime_high_bids
  , SUM(
        CASE fb.high_bids
          WHEN 1
          THEN bid_amount
          ELSE 0
        END) AS lifetime_high_bid_amount
  , AVG(
        CASE fb.high_bids
          WHEN 1
          THEN bid_amount
          ELSE 0
        END) AS average_high_bid_amount
  , COUNT(DISTINCT fb.auc_id) AS lifetime_auctions
  , MIN(CONVERT(datetime, fb.bid_date, 120)) AS first_bid
  , MIN(
      CASE fb.high_bids
         WHEN 1 THEN CONVERT(datetime, fb.bid_date, 120)
         ELSE NULL
      END
    ) AS first_purchase
  , MAX(CONVERT(datetime, fb.bid_date, 120)) AS last_bid
FROM fact_bids AS fb
GROUP BY fb.dim_bidder_user_id
    persist_for: 3 hours
  fields:
  - dimension: user_id
    type: int
    primary_key: true
    sql: ${TABLE}.user_id
  - dimension: lifetime_bids
    type: number
    sql: ${TABLE}.lifetime_bids
  - dimension: lifetime_high_bids
    type: number
    sql: ${TABLE}.lifetime_high_bids
  - dimension: lifetime_high_bid_amount
    type: number
    sql: ${TABLE}.lifetime_high_bid_amount
  - dimension: average_high_bid_amount
    type: number
    sql: ROUND(${TABLE}.average_high_bid_amount,0)
  - dimension: lifetime_auctions
    type: number
    sql: ${TABLE}.lifetime_auctions
  - dimension: lifetime_share_high_bid
    type: int
    sql: 100 * ${lifetime_high_bids}/${lifetime_auctions}
  - dimension: lifetime_winning_bids_tiered
    type: tier
    sql: ${lifetime_high_bids}
  - dimension_group: first_bid
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    sql: CONVERT(datetime, ${TABLE}.first_bid)
  - dimension_group: last_bid
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    sql: CONVERT(datetime, ${TABLE}.last_bid)
  - measure: count
    type: count
    drill_fields:
    - detail*

Contents

  • buyer_bid_facts.sql
  • buyer_bid_facts.sql
  • buyer_bid_facts.view.lkml
  • buyer_bid_facts.view.lookml