Winnings

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

SELECT
  id
  ,UserId
  ,Amount
  ,CreatedAt
  ,UNIX_TIMESTAMP(DATE(CreatedAt)) as created_date_unix
  ,WLSId
  ,RoundId
  ,TransType
  FROM Transactions AS transactions
WHERE TransType = 3

UNION ALL

SELECT
  id
  ,UserId
  ,Amount
  ,CreatedAt
  ,UNIX_TIMESTAMP(DATE(CreatedAt)) as created_date_unix
  ,WLSId
  ,RoundId
  ,TransType
  FROM Transactions_Archive AS transactions_archive
 WHERE TransType = 3

# models/schema.yml
version: 2

models:
  - name: winnings
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: user_id
      - name: transtype
      - name: wlsid
      - name: RoundId
      - name: inr2usd
      - name: country
      - name: Category
      - name: New_Repeat_winners_monthly
      - name: New_Repeat_winners_weekly
      - name: New_Repeat_winners_daily
      - name: Winnings_Per_User
      - name: Is_Organic
      - name: Is_Growth
      - name: Is_Marketing
      - name: Active_Flag
      - name: Device

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
  id
  ,UserId
  ,Amount
  ,CreatedAt
  ,UNIX_TIMESTAMP(DATE(CreatedAt)) as created_date_unix
  ,WLSId
  ,RoundId
  ,TransType
  FROM Transactions AS transactions
WHERE TransType = 3

UNION ALL

SELECT
  id
  ,UserId
  ,Amount
  ,CreatedAt
  ,UNIX_TIMESTAMP(DATE(CreatedAt)) as created_date_unix
  ,WLSId
  ,RoundId
  ,TransType
  FROM Transactions_Archive AS transactions_archive
 WHERE TransType = 3

view: winnings {
  derived_table: {
    sql: SELECT
  id
  ,UserId
  ,Amount
  ,CreatedAt
  ,UNIX_TIMESTAMP(DATE(CreatedAt)) as created_date_unix
  ,WLSId
  ,RoundId
  ,TransType
  FROM Transactions AS transactions
WHERE TransType = 3

UNION ALL

SELECT
  id
  ,UserId
  ,Amount
  ,CreatedAt
  ,UNIX_TIMESTAMP(DATE(CreatedAt)) as created_date_unix
  ,WLSId
  ,RoundId
  ,TransType
  FROM Transactions_Archive AS transactions_archive
 WHERE TransType = 3 ;;
  }

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

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

  dimension: transtype {
    type: int
    sql: ${TABLE}.TransType ;;
  }

  dimension: wlsid {
    type: number
    hidden: yes
    sql: ${TABLE}.WLSId ;;
  }

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

  dimension: inr2usd {
    type: number
    hidden: yes
    sql: case when ${country}="India"  THEN ${DailyCurrencyRate.conversion} else 1 END ;;
  }

  dimension: country {
    type: string
    sql: Case when ${TABLE}.WLSId in (2,6,9,12,28,29) Then "US" else "India" End ;;
  }

  dimension: Category {
    type: string
    sql: ${userstatus.category} ;;
  }

  dimension: New_Repeat_winners_monthly {
    type: string
    sql: case WHEN ${created_at_month} = ${userstatus.first_league_join_date_month} Then "New " Else "Repeat " End ;;
  }

  dimension: New_Repeat_winners_weekly {
    type: string
    sql: case WHEN ${created_at_week} = ${userstatus.first_league_join_date_week} Then "New " Else "Repeat " End ;;
  }

  dimension: New_Repeat_winners_daily {
    type: string
    sql: case WHEN ${created_at_date} = ${userstatus.first_league_join_date_date} Then "New " Else "Repeat " End ;;
  }

  dimension: Winnings_Per_User {
    type: number
    value_format: "$#,##0.00"
    drill_fields: [detail*]
    sql: ${winnings_amount}/${winners_count} ;;
  }

  dimension: Is_Organic {
    type: yesno
    hidden: yes
    sql: ${userstatus.category}="ORGANIC" ;;
  }

  dimension: Is_Growth {
    type: yesno
    hidden: yes
    sql: ${userstatus.category}="GROWTH" ;;
  }

  dimension: Is_Marketing {
    type: yesno
    hidden: yes
    sql: ${userstatus.category}="MARKETING" ;;
  }

  dimension: Active_Flag {
    type: string
    sql: CASE When ${userstatus.Status}= 1 THEN "Active " ELSE "Deactivated " ;;
  }

  dimension: Device {
    type: string
    sql: ${userstatus.Device} ;;
  }

  dimension_group: created_at {
    type: time
    timeframes: [date, week, month, year, time]
    datatype: datetime
    sql: ${TABLE}.CreatedAt ;;
  }

  dimension_group: created_date_unix {
    label: "League Join Date"
    type: time
    timeframes: [date, raw]
    sql: ${TABLE}.created_date_unix ;;
  }

  measure: winnings_amount {
    type: sum
    value_format: "$#,##0.00"
    drill_fields: [detail*]
    sql: ${TABLE}.Amount ;;
  }

  measure: winners_count {
    type: count_distinct
    drill_fields: [detail*]
    sql: ${user_id} ;;
  }

  measure: Organic_User_count {
    type: count_distinct
    filters: [userstatus.category: "Organic"]
    sql: ${user_id} ;;
  }

  measure: Referral_User_count {
    type: count_distinct
    filters: [userstatus.category: "Referral"]
    sql: ${user_id} ;;
  }

  measure: Growth_User_count {
    type: count_distinct
    filters: [userstatus.category: "Growth"]
    sql: ${user_id} ;;
  }

  measure: Marketing_User_count {
    type: count_distinct
    filters: [userstatus.category: "Marketing"]
    sql: ${user_id} ;;
  }
}

- view: winnings
  derived_table:
    sql: SELECT
  id
  ,UserId
  ,Amount
  ,CreatedAt
  ,UNIX_TIMESTAMP(DATE(CreatedAt)) as created_date_unix
  ,WLSId
  ,RoundId
  ,TransType
  FROM Transactions AS transactions
WHERE TransType = 3

UNION ALL

SELECT
  id
  ,UserId
  ,Amount
  ,CreatedAt
  ,UNIX_TIMESTAMP(DATE(CreatedAt)) as created_date_unix
  ,WLSId
  ,RoundId
  ,TransType
  FROM Transactions_Archive AS transactions_archive
 WHERE TransType = 3
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: user_id
    type: int
    sql: ${TABLE}.UserId
  - dimension: transtype
    type: int
    sql: ${TABLE}.TransType
  - dimension: wlsid
    type: number
    hidden: true
    sql: ${TABLE}.WLSId
  - dimension: RoundId
    type: int
    sql: ${TABLE}.RoundId
  - dimension: inr2usd
    type: number
    hidden: true
    sql: case when ${country}="India"  THEN ${DailyCurrencyRate.conversion} else 1 END
  - dimension: country
    type: string
    sql: Case when ${TABLE}.WLSId in (2,6,9,12,28,29) Then "US" else "India" End
  - dimension: Category
    type: string
    sql: ${userstatus.category}
  - dimension: New_Repeat_winners_monthly
    type: string
    sql: case WHEN ${created_at_month} = ${userstatus.first_league_join_date_month} Then "New " Else "Repeat " End
  - dimension: New_Repeat_winners_weekly
    type: string
    sql: case WHEN ${created_at_week} = ${userstatus.first_league_join_date_week} Then "New " Else "Repeat " End
  - dimension: New_Repeat_winners_daily
    type: string
    sql: case WHEN ${created_at_date} = ${userstatus.first_league_join_date_date} Then "New " Else "Repeat " End
  - dimension: Winnings_Per_User
    type: number
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: ${winnings_amount}/${winners_count}
  - dimension: Is_Organic
    type: yesno
    hidden: true
    sql: ${userstatus.category}="ORGANIC"
  - dimension: Is_Growth
    type: yesno
    hidden: true
    sql: ${userstatus.category}="GROWTH"
  - dimension: Is_Marketing
    type: yesno
    hidden: true
    sql: ${userstatus.category}="MARKETING"
  - dimension: Active_Flag
    type: string
    sql: CASE When ${userstatus.Status}= 1 THEN "Active " ELSE "Deactivated "
  - dimension: Device
    type: string
    sql: ${userstatus.Device}
  - dimension_group: created_at
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    - time
    datatype: datetime
    sql: ${TABLE}.CreatedAt
  - dimension_group: created_date_unix
    type: time
    timeframes:
    - date
    - raw
    sql: ${TABLE}.created_date_unix
  - measure: winnings_amount
    type: sum
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: ${TABLE}.Amount
  - measure: winners_count
    type: count_distinct
    drill_fields:
    - detail*
    sql: ${user_id}
  - measure: Organic_User_count
    type: count_distinct
    filters:
    - userstatus.category: Organic
    sql: ${user_id}
  - measure: Referral_User_count
    type: count_distinct
    filters:
    - userstatus.category: Referral
    sql: ${user_id}
  - measure: Growth_User_count
    type: count_distinct
    filters:
    - userstatus.category: Growth
    sql: ${user_id}
  - measure: Marketing_User_count
    type: count_distinct
    filters:
    - userstatus.category: Marketing
    sql: ${user_id}