dbt Marketplace automotive

Seller Listing Facts

Seller Listing Facts model for marketplace automotive.

inferred

Blocks

dbt SQL LKML LookML

seller_listing_facts.sql · 62 lines

inferred

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

SELECT fl.dim_seller_user_id AS user_id
  , COUNT(*) AS lifetime_listings
  , MIN(CONVERT(datetime, fl.auction_end_date)) AS first_auction
  , MAX(CONVERT(datetime, fl.auction_end_date)) AS last_auction
  , SUM(
      CASE
        WHEN fl.listing_status NOT IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.high_bid_amount_usd)
        ELSE 0
      END) AS lifetime_amount_not_sold
  , SUM(
      CASE
        WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.high_bid_amount_usd)
        ELSE 0
      END) AS lifetime_amount_sold
  , AVG(
      CASE
        WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.strike_price) - CONVERT(float, fl.starting_price )
        ELSE NULL
      END) AS average_start_strike_differential
  , AVG(
      CASE
        WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.high_bid_amount_usd) - CONVERT(float, fl.starting_price )
        ELSE NULL
      END) AS average_start_close_differential
  , MAX(fl.sale_attempts) AS maximum_sale_attempts_single_listing
  , SUM(CONVERT(float, fl.seller_commission)) AS lifetime_commission
  , MIN(
      CASE
         WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' ) THEN CONVERT(datetime, fl.auction_end_date)
         ELSE NULL
      END) AS first_sale

FROM fact_listing AS fl
GROUP BY fl.dim_seller_user_id

# models/schema.yml
version: 2

models:
  - name: seller_listing_facts
    columns:
      - name: user_id
        tests:
          - unique
          - not_null
      - name: lifetime_listings
      - name: lifetime_listings_tiered
      - name: lifetime_amount_not_sold
      - name: first_sale
      - name: lifetime_amount_sold
      - name: lifetime_amount_sold_tiered
      - name: average_start_close_differential
      - name: average_start_strike_differential
      - name: maximum_sale_attempts_single_listing
      - name: lifetime_commission

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.

seller_listing_facts.sql · 38 lines

source-preserved validation: parses

SELECT fl.dim_seller_user_id AS user_id
  , COUNT(*) AS lifetime_listings
  , MIN(CONVERT(datetime, fl.auction_end_date)) AS first_auction
  , MAX(CONVERT(datetime, fl.auction_end_date)) AS last_auction
  , SUM(
      CASE
        WHEN fl.listing_status NOT IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.high_bid_amount_usd)
        ELSE 0
      END) AS lifetime_amount_not_sold
  , SUM(
      CASE
        WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.high_bid_amount_usd)
        ELSE 0
      END) AS lifetime_amount_sold
  , AVG(
      CASE
        WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.strike_price) - CONVERT(float, fl.starting_price )
        ELSE NULL
      END) AS average_start_strike_differential
  , AVG(
      CASE
        WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.high_bid_amount_usd) - CONVERT(float, fl.starting_price )
        ELSE NULL
      END) AS average_start_close_differential
  , MAX(fl.sale_attempts) AS maximum_sale_attempts_single_listing
  , SUM(CONVERT(float, fl.seller_commission)) AS lifetime_commission
  , MIN(
      CASE
         WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' ) THEN CONVERT(datetime, fl.auction_end_date)
         ELSE NULL
      END) AS first_sale

FROM fact_listing AS fl
GROUP BY fl.dim_seller_user_id

seller_listing_facts.view.lkml · 116 lines

inferred

view: seller_listing_facts {
  derived_table: {
    sql: SELECT fl.dim_seller_user_id AS user_id
  , COUNT(*) AS lifetime_listings
  , MIN(CONVERT(datetime, fl.auction_end_date)) AS first_auction
  , MAX(CONVERT(datetime, fl.auction_end_date)) AS last_auction
  , SUM(
      CASE
        WHEN fl.listing_status NOT IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.high_bid_amount_usd)
        ELSE 0
      END) AS lifetime_amount_not_sold
  , SUM(
      CASE
        WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.high_bid_amount_usd)
        ELSE 0
      END) AS lifetime_amount_sold
  , AVG(
      CASE
        WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.strike_price) - CONVERT(float, fl.starting_price )
        ELSE NULL
      END) AS average_start_strike_differential
  , AVG(
      CASE
        WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.high_bid_amount_usd) - CONVERT(float, fl.starting_price )
        ELSE NULL
      END) AS average_start_close_differential
  , MAX(fl.sale_attempts) AS maximum_sale_attempts_single_listing
  , SUM(CONVERT(float, fl.seller_commission)) AS lifetime_commission
  , MIN(
      CASE
         WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' ) THEN CONVERT(datetime, fl.auction_end_date)
         ELSE NULL
      END) AS first_sale

FROM fact_listing AS fl
GROUP BY fl.dim_seller_user_id ;;
    persist_for: "3 hours"
  }

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

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

  dimension: lifetime_listings_tiered {
    type: tier
    sql: ${lifetime_listings} ;;
  }

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

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

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

  dimension: lifetime_amount_sold_tiered {
    type: tier
    sql: ${lifetime_amount_sold} ;;
  }

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

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

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

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

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

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

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

seller_listing_facts.view.lookml · 97 lines

source-preserved

- view: seller_listing_facts
  derived_table:
    sql: SELECT fl.dim_seller_user_id AS user_id
  , COUNT(*) AS lifetime_listings
  , MIN(CONVERT(datetime, fl.auction_end_date)) AS first_auction
  , MAX(CONVERT(datetime, fl.auction_end_date)) AS last_auction
  , SUM(
      CASE
        WHEN fl.listing_status NOT IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.high_bid_amount_usd)
        ELSE 0
      END) AS lifetime_amount_not_sold
  , SUM(
      CASE
        WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.high_bid_amount_usd)
        ELSE 0
      END) AS lifetime_amount_sold
  , AVG(
      CASE
        WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.strike_price) - CONVERT(float, fl.starting_price )
        ELSE NULL
      END) AS average_start_strike_differential
  , AVG(
      CASE
        WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' )
        THEN CONVERT(float, fl.high_bid_amount_usd) - CONVERT(float, fl.starting_price )
        ELSE NULL
      END) AS average_start_close_differential
  , MAX(fl.sale_attempts) AS maximum_sale_attempts_single_listing
  , SUM(CONVERT(float, fl.seller_commission)) AS lifetime_commission
  , MIN(
      CASE
         WHEN fl.listing_status IN ( 'Sold', 'Collapsed sale' ) THEN CONVERT(datetime, fl.auction_end_date)
         ELSE NULL
      END) AS first_sale

FROM fact_listing AS fl
GROUP BY fl.dim_seller_user_id
    persist_for: 3 hours
  fields:
  - dimension: user_id
    type: number
    primary_key: true
    sql: ${TABLE}.user_id
  - dimension: lifetime_listings
    type: number
    sql: ${TABLE}.lifetime_listings
  - dimension: lifetime_listings_tiered
    type: tier
    sql: ${lifetime_listings}
  - dimension: lifetime_amount_not_sold
    type: number
    sql: ${TABLE}.lifetime_amount_not_sold
  - dimension: first_sale
    type: time
    sql: ${TABLE}.first_sale
  - dimension: lifetime_amount_sold
    type: number
    sql: ${TABLE}.lifetime_amount_sold
  - dimension: lifetime_amount_sold_tiered
    type: tier
    sql: ${lifetime_amount_sold}
  - dimension: average_start_close_differential
    type: number
    sql: ${TABLE}.average_start_close_differential
  - dimension: average_start_strike_differential
    type: number
    sql: ${TABLE}.average_start_strike_differential
  - dimension: maximum_sale_attempts_single_listing
    type: number
    sql: ${TABLE}.maximum_sale_attempts_single_listing
  - dimension: lifetime_commission
    type: number
    sql: ${TABLE}.lifetime_commission
  - dimension_group: first_auction
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_auction
  - dimension_group: last_auction
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    sql: ${TABLE}.last_auction
  - measure: count
    type: count
    drill_fields:
    - detail*

seller_listing_facts.sql
seller_listing_facts.sql
seller_listing_facts.view.lkml
seller_listing_facts.view.lookml

Seller Listing Facts

Blocks

Assumptions

Contents