SQL Gaming fantasy

Entry Fees

Entry Fees model for gaming fantasy.

source-preserved

Blocks

dbt SQL LKML LookML

entry_fees.sql · 68 lines

inferred

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

SELECT
  id
  ,UserId
  ,Amount
  , CreatedAt
  , UNIX_TIMESTAMP(DATE(CreatedAt)) AS created_date_unix
  ,TransType
  ,Currency
  ,RoundId
  ,WLSId
  ,LeagueId
  ,TourId
  ,UserAgent
  FROM Transactions AS transactions
  WHERE TransType=1

UNION ALL

SELECT
  id
  ,UserId
  ,Amount
  , CreatedAt
  , UNIX_TIMESTAMP(DATE(CreatedAt)) AS created_date_unix
  ,TransType
  ,Currency
  ,RoundId
  ,WLSId
  ,LeagueId
  ,TourId
  ,UserAgent
  FROM Transactions_Archive AS transactions_archive
  WHERE TransType=1

# models/schema.yml
version: 2

models:
  - name: entry_fees
    columns:
      - name: id
      - name: user_id
      - name: wlsid
      - name: RoundId
      - name: LeagueId
      - name: TourId
      - name: inr2usd
      - name: amount
      - name: amount_segments
      - name: amount_usd
      - name: country
      - name: AEFPU_USD
      - name: Category
      - name: New_Repeat_User_monthly
      - name: New_Repeat_User_weekly
      - name: New_Repeat_User_daily
      - name: New_Repeat_User_yearly
      - name: AEFPU
      - name: Is_Organic
      - name: Is_Growth
      - name: Is_Marketing
      - name: Active_Flag
      - name: Device
      - name: Game
      - name: user_agent

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.

entry_fees.sql · 33 lines

source-preserved validation: parses

SELECT
  id
  ,UserId
  ,Amount
  , CreatedAt
  , UNIX_TIMESTAMP(DATE(CreatedAt)) AS created_date_unix
  ,TransType
  ,Currency
  ,RoundId
  ,WLSId
  ,LeagueId
  ,TourId
  ,UserAgent
  FROM Transactions AS transactions
  WHERE TransType=1

UNION ALL

SELECT
  id
  ,UserId
  ,Amount
  , CreatedAt
  , UNIX_TIMESTAMP(DATE(CreatedAt)) AS created_date_unix
  ,TransType
  ,Currency
  ,RoundId
  ,WLSId
  ,LeagueId
  ,TourId
  ,UserAgent
  FROM Transactions_Archive AS transactions_archive
  WHERE TransType=1

entry_fees.view.lkml · 282 lines

inferred

view: entry_fees {
  derived_table: {
    sql: SELECT
  id
  ,UserId
  ,Amount
  , CreatedAt
  , UNIX_TIMESTAMP(DATE(CreatedAt)) AS created_date_unix
  ,TransType
  ,Currency
  ,RoundId
  ,WLSId
  ,LeagueId
  ,TourId
  ,UserAgent
  FROM Transactions AS transactions
  WHERE TransType=1

UNION ALL

SELECT
  id
  ,UserId
  ,Amount
  , CreatedAt
  , UNIX_TIMESTAMP(DATE(CreatedAt)) AS created_date_unix
  ,TransType
  ,Currency
  ,RoundId
  ,WLSId
  ,LeagueId
  ,TourId
  ,UserAgent
  FROM Transactions_Archive AS transactions_archive
  WHERE TransType=1 ;;
  }

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

  dimension: user_id {
    label: "UserID"
    type: int
    drill_fields: [detail*]
    sql: ${TABLE}.UserId ;;
  }

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

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

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

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

  dimension: inr2usd {
    type: number
    hidden: yes
    sql: case when ${TABLE}.WLSId NOT IN (2,6,9,12,28,29)  THEN ${DailyCurrencyRate.conversion} else 1 END ;;
  }

  dimension: amount {
    label: "Entry Fees in Rupees"
    type: number
    value_format: "\"₹\"#,##0.00"
    sql: ${TABLE}.Amount ;;
  }

  dimension: amount_segments {
    type: tier
    value_format: "\"₹\"#,##0.00"
    sql: ${amount} ;;
  }

  dimension: amount_usd {
    label: "Entry Fees in USD"
    type: number
    value_format: "$#,##0.00"
    sql: (${TABLE}.Amount * ${inr2usd}) ;;
  }

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

  dimension: AEFPU_USD {
    label: "Average Entry Fees Per User USD"
    type: number
    value_format: "$#,##0.00"
    drill_fields: [detail*]
    sql: ${Entry_Fees_USD}/${User_Count} ;;
  }

  dimension: Category {
    label: "Category (Marketing/Organic/Growth/Referral)"
    type: string
    sql: ${userstatus.category} ;;
  }

  dimension: New_Repeat_User_monthly {
    label: "New Or Repeat User MonthWise"
    type: string
    sql: case WHEN ${created_at_month} = ${userstatus.first_league_join_date_month} Then "New " Else "Repeat " End ;;
  }

  dimension: New_Repeat_User_weekly {
    label: "New Or Repeat User WeekWise"
    type: string
    sql: case WHEN ${created_at_week} = ${userstatus.first_league_join_date_week} Then "New " Else "Repeat " End ;;
  }

  dimension: New_Repeat_User_daily {
    label: "New Or Repeat User Daily"
    type: string
    sql: case WHEN ${created_at_date} = ${userstatus.first_league_join_date_date} Then "New " Else "Repeat " End ;;
  }

  dimension: New_Repeat_User_yearly {
    label: "New Or Repeat User YearWise"
    type: string
    sql: case WHEN ${created_at_year} = ${userstatus.first_league_join_date_year} Then "New " Else "Repeat " End ;;
  }

  dimension: AEFPU {
    label: "Average Entry Fees Per User"
    type: number
    value_format: "$#,##0.00"
    drill_fields: [detail*]
    sql: ${Entry_Fees}/${User_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 {
    label: "Active or Deactivated"
    type: string
    sql: CASE When ${userstatus.Status}= 1 THEN "Active " ELSE "Deactivated "  END ;;
  }

  dimension: Device {
    label: "Device (Mobile OR Desktop)"
    type: string
    sql: ${userstatus.Device} ;;
  }

  dimension: Game {
    label: "Game (Cricket/Football/Rugby)"
    type: string
    sql: Case when ${TABLE}.WLSId in (1,2,5,6,7,8,13,15,16,17,18,19,20,21,22,23,24) Then "Cricket" when ${TABLE}.WLSId in (9,10,11,12,14,25,26) Then "Football" when ${TABLE}.WLSId in (27,28,29,30) Then "Rugby"  End ;;
  }

  dimension: user_agent {
    label: "Browser"
    type: string
    sql: ${TABLE}.UserAgent ;;
  }

  dimension_group: created_at {
    label: "League Join Date"
    type: time
    timeframes: [date, week, month, year, time, raw]
    datatype: datetime
    sql: ${TABLE}.CreatedAt ;;
  }

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

  measure: Entry_Fees_Dollar {
    label: "Total Entry Fees In Dollar"
    type: sum
    value_format: "$#,##0.00"
    drill_fields: [detail*]
    sql: (${amount} * ${inr2usd}) ;;
  }

  measure: Entry_Fees {
    label: "Total Entry Fees In Rupees"
    type: sum
    value_format: "\"₹\"#,##0.00"
    drill_fields: [detail*]
    sql: ${amount} ;;
  }

  measure: Entry_Fees_USD {
    label: "Total Entry Fees In Rupees"
    type: sum
    value_format: "$#,##0.00"
    drill_fields: [detail*]
    sql: ${amount_usd} ;;
  }

  measure: User_Count {
    label: "Total Paid Users"
    type: count_distinct
    drill_fields: [userstatus.userid, userstatus.user_full_name, userstatus.email_id, userstatus.date_of_registration]
    sql: ${user_id} ;;
  }

  measure: league_join_count {
    label: "Total League Joining Events"
    type: count
  }

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

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

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

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

  measure: virality {
    label: "Virality- Paid Users"
    type: number
    sql: 1.0 * (${Organic_User_count} + ${Growth_User_count} +${Referral_User_count}) / ${Marketing_User_count} ;;
  }

  measure: Round_count {
    type: count_distinct
    sql: ${TABLE}.RoundId ;;
  }
}

entry_fees.view.lookml · 221 lines

source-preserved

- view: entry_fees
  derived_table:
    sql: SELECT
  id
  ,UserId
  ,Amount
  , CreatedAt
  , UNIX_TIMESTAMP(DATE(CreatedAt)) AS created_date_unix
  ,TransType
  ,Currency
  ,RoundId
  ,WLSId
  ,LeagueId
  ,TourId
  ,UserAgent
  FROM Transactions AS transactions
  WHERE TransType=1

UNION ALL

SELECT
  id
  ,UserId
  ,Amount
  , CreatedAt
  , UNIX_TIMESTAMP(DATE(CreatedAt)) AS created_date_unix
  ,TransType
  ,Currency
  ,RoundId
  ,WLSId
  ,LeagueId
  ,TourId
  ,UserAgent
  FROM Transactions_Archive AS transactions_archive
  WHERE TransType=1
  fields:
  - dimension: id
    type: int
    hidden: true
    sql: ${TABLE}.id
  - dimension: user_id
    type: int
    label: UserID
    drill_fields:
    - detail*
    sql: ${TABLE}.UserId
  - dimension: wlsid
    type: number
    hidden: true
    sql: ${TABLE}.WLSId
  - dimension: RoundId
    type: int
    sql: ${TABLE}.RoundId
  - dimension: LeagueId
    type: int
    sql: ${TABLE}.LeagueId
  - dimension: TourId
    type: int
    sql: ${TABLE}.TourId
  - dimension: inr2usd
    type: number
    hidden: true
    sql: case when ${TABLE}.WLSId NOT IN (2,6,9,12,28,29)  THEN ${DailyCurrencyRate.conversion} else 1 END
  - dimension: amount
    type: number
    label: Entry Fees in Rupees
    value_format: '"₹"#,##0.00'
    sql: ${TABLE}.Amount
  - dimension: amount_segments
    type: tier
    value_format: '"₹"#,##0.00'
    sql: ${amount}
  - dimension: amount_usd
    type: number
    label: Entry Fees in USD
    value_format: '$#,##0.00'
    sql: (${TABLE}.Amount * ${inr2usd})
  - dimension: country
    type: string
    label: Country (India Or US)
    sql: Case when ${TABLE}.WLSId in (2,6,9,12,28,29) Then "US" else "India" End
  - dimension: AEFPU_USD
    type: number
    label: Average Entry Fees Per User USD
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: ${Entry_Fees_USD}/${User_Count}
  - dimension: Category
    type: string
    label: Category (Marketing/Organic/Growth/Referral)
    sql: ${userstatus.category}
  - dimension: New_Repeat_User_monthly
    type: string
    label: New Or Repeat User MonthWise
    sql: case WHEN ${created_at_month} = ${userstatus.first_league_join_date_month} Then "New " Else "Repeat " End
  - dimension: New_Repeat_User_weekly
    type: string
    label: New Or Repeat User WeekWise
    sql: case WHEN ${created_at_week} = ${userstatus.first_league_join_date_week} Then "New " Else "Repeat " End
  - dimension: New_Repeat_User_daily
    type: string
    label: New Or Repeat User Daily
    sql: case WHEN ${created_at_date} = ${userstatus.first_league_join_date_date} Then "New " Else "Repeat " End
  - dimension: New_Repeat_User_yearly
    type: string
    label: New Or Repeat User YearWise
    sql: case WHEN ${created_at_year} = ${userstatus.first_league_join_date_year} Then "New " Else "Repeat " End
  - dimension: AEFPU
    type: number
    label: Average Entry Fees Per User
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: ${Entry_Fees}/${User_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
    label: Active or Deactivated
    sql: CASE When ${userstatus.Status}= 1 THEN "Active " ELSE "Deactivated "  END
  - dimension: Device
    type: string
    label: Device (Mobile OR Desktop)
    sql: ${userstatus.Device}
  - dimension: Game
    type: string
    label: Game (Cricket/Football/Rugby)
    sql: Case when ${TABLE}.WLSId in (1,2,5,6,7,8,13,15,16,17,18,19,20,21,22,23,24) Then "Cricket" when ${TABLE}.WLSId in (9,10,11,12,14,25,26) Then "Football" when ${TABLE}.WLSId in (27,28,29,30) Then "Rugby"  End
  - dimension: user_agent
    type: string
    label: Browser
    sql: ${TABLE}.UserAgent
  - dimension_group: created_at
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    - time
    - raw
    datatype: datetime
    sql: ${TABLE}.CreatedAt
  - dimension_group: created_date_unix
    type: time
    timeframes:
    - date
    - raw
    datatype: epoch
    sql: ${TABLE}.created_date_unix
  - measure: Entry_Fees_Dollar
    type: sum
    label: Total Entry Fees In Dollar
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: (${amount} * ${inr2usd})
  - measure: Entry_Fees
    type: sum
    label: Total Entry Fees In Rupees
    value_format: '"₹"#,##0.00'
    drill_fields:
    - detail*
    sql: ${amount}
  - measure: Entry_Fees_USD
    type: sum
    label: Total Entry Fees In Rupees
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: ${amount_usd}
  - measure: User_Count
    type: count_distinct
    label: Total Paid Users
    drill_fields:
    - userstatus.userid
    - userstatus.user_full_name
    - userstatus.email_id
    - userstatus.date_of_registration
    sql: ${user_id}
  - measure: league_join_count
    type: count
    label: Total League Joining Events
  - 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}
  - measure: virality
    type: number
    label: Virality- Paid Users
    sql: 1.0 * (${Organic_User_count} + ${Growth_User_count} +${Referral_User_count}) / ${Marketing_User_count}
  - measure: Round_count
    type: count_distinct
    sql: ${TABLE}.RoundId

entry_fees.sql
entry_fees.sql
entry_fees.view.lkml
entry_fees.view.lookml

Entry Fees

Blocks

Assumptions

Contents