SQL Gaming fantasy

Userstatus

Userstatus model for gaming fantasy.

source-preserved

Blocks

dbt SQL LKML LookML

userstatus.sql · 54 lines

inferred

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

select entryfees.UserId,reg.EmailId,reg.MobileNum,reg.UtmRef,
reg.Gender,reg.Status,reg.UtmFullString,
reg.updated_from, entryfees.firstleaguedate,
entryfees.lastactivedate, entryfees.TotalEntryFee,
entryfees.LeaguesPlayed,reg.CreatedDate,
reg.IsCommEmailVerified,
reg.IsMobileVerified,
reg.RegFromWLSId,
reg.CurSiteVersion,
reg.City,
entryfees.Amount
from
(SELECT
UserId
,Amount
,MIN(CreatedAt) AS firstleaguedate
,MAX(CreatedAt) As lastactivedate
,SUM(Amount) AS TotalEntryFee
,COUNT(CreatedAt) AS LeaguesPlayed
 FROM {{ ref('entry_fees') }}
 GROUP BY UserId ORDER BY CreatedAt ASC) as entryfees left join UserRegistration as reg on
 entryfees.UserId=reg.id

# models/schema.yml
version: 2

models:
  - name: userstatus
    columns:
      - name: userid
      - name: Email_Id
      - name: Mobile
      - name: City
      - name: Status
      - name: Total_EntryFees_Per_User
      - name: Leagues_Played
      - name: firstfees
      - name: days_since_last_activity
      - name: reg_firstpaid_diff
      - name: gender
      - name: is_comm_email_verified
      - name: is_mobile_verified
      - name: reg_from_wlsid
      - name: state_id
      - name: updated_from
      - name: site_version
      - name: utm_fullstring
      - name: utmref
      - name: Device
      - name: Referral_Categories
      - name: category

Assumptions

Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
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.

userstatus.sql · 22 lines

source-preserved validation: not checked

select entryfees.UserId,reg.EmailId,reg.MobileNum,reg.UtmRef,
reg.Gender,reg.Status,reg.UtmFullString,
reg.updated_from, entryfees.firstleaguedate,
entryfees.lastactivedate, entryfees.TotalEntryFee,
entryfees.LeaguesPlayed,reg.CreatedDate,
reg.IsCommEmailVerified,
reg.IsMobileVerified,
reg.RegFromWLSId,
reg.CurSiteVersion,
reg.City,
entryfees.Amount
from
(SELECT
UserId
,Amount
,MIN(CreatedAt) AS firstleaguedate
,MAX(CreatedAt) As lastactivedate
,SUM(Amount) AS TotalEntryFee
,COUNT(CreatedAt) AS LeaguesPlayed
 FROM ${entry_fees.SQL_TABLE_NAME}
 GROUP BY UserId ORDER BY CreatedAt ASC) as entryfees left join UserRegistration as reg on
 entryfees.UserId=reg.id

userstatus.view.lkml · 180 lines

inferred

view: userstatus {
  derived_table: {
    sql: select entryfees.UserId,reg.EmailId,reg.MobileNum,reg.UtmRef,
reg.Gender,reg.Status,reg.UtmFullString,
reg.updated_from, entryfees.firstleaguedate,
entryfees.lastactivedate, entryfees.TotalEntryFee,
entryfees.LeaguesPlayed,reg.CreatedDate,
reg.IsCommEmailVerified,
reg.IsMobileVerified,
reg.RegFromWLSId,
reg.CurSiteVersion,
reg.City,
entryfees.Amount
from
(SELECT
UserId
,Amount
,MIN(CreatedAt) AS firstleaguedate
,MAX(CreatedAt) As lastactivedate
,SUM(Amount) AS TotalEntryFee
,COUNT(CreatedAt) AS LeaguesPlayed
 FROM ${entry_fees.SQL_TABLE_NAME}
 GROUP BY UserId ORDER BY CreatedAt ASC) as entryfees left join UserRegistration as reg on
 entryfees.UserId=reg.id ;;
  }

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

  dimension: Email_Id {
    sql: ${TABLE}.EmailId ;;
  }

  dimension: Mobile {
    hidden: yes
    sql: ${TABLE}.MobileNum ;;
  }

  dimension: City {
    hidden: yes
    sql: ${TABLE}.City ;;
  }

  dimension: Status {
    label: "Non Fraud User"
    type: yesno
    sql: ${TABLE}.Status ;;
  }

  dimension: Total_EntryFees_Per_User {
    type: number
    value_format: "\"₹\"#.00"
    sql: ${TABLE}.TotalEntryFee ;;
  }

  dimension: Leagues_Played {
    type: number
    sql: ${TABLE}.LeaguesPlayed ;;
  }

  dimension: firstfees {
    label: "First Entry Fees"
    type: number
    sql: ${TABLE}.Amount ;;
  }

  dimension: days_since_last_activity {
    type: number
    sql: DateDiff(Now(),${TABLE}.lastactivedate) ;;
  }

  dimension: reg_firstpaid_diff {
    label: "Days Difference (Reg. To FirstPaid)"
    type: number
    sql: DateDiff(${TABLE}.firstleaguedate,${TABLE}.Createddate) ;;
  }

  dimension: gender {
    sql: ${TABLE}.Gender ;;
  }

  dimension: is_comm_email_verified {
    type: yesno
    sql: ${TABLE}.IsCommEmailVerified ;;
  }

  dimension: is_mobile_verified {
    type: yesno
    sql: ${TABLE}.IsMobileVerified ;;
  }

  dimension: reg_from_wlsid {
    type: int
    sql: ${TABLE}.RegFromWLSId ;;
  }

  dimension: state_id {
    type: int
    sql: ${TABLE}.StateId ;;
  }

  dimension: updated_from {
    type: string
    hidden: yes
    sql: ${TABLE}.updated_from ;;
  }

  dimension: site_version {
    type: string
    sql: ${TABLE}.CurSiteVersion ;;
  }

  dimension: utm_fullstring {
    sql: ${TABLE}.UtmFullString ;;
  }

  dimension: utmref {
    type: number
    sql: ${TABLE}.UtmRef ;;
  }

  dimension: Device {
    type: string
    sql: CASE WHEN ${TABLE}.Updated_From In ("D","DFC","DGC","DYC","Direct","F","Facebook Connect","Google Connect") THEN "Desktop " ELSE "Mobile " END ;;
  }

  dimension: Referral_Categories {
    type: string
    sql: Case When  ${TABLE}.UtmFullString LIKE '%whatsapp%' Then "WhatsApp" When  ${TABLE}.UtmFullString LIKE '%facebook%' Then "FaceBook" When  ${TABLE}.UtmFullString LIKE '%email%' then  "Email" When ${TABLE}.UtmFullString LIKE '%link%' then "Link" When ${TABLE}.UtmFullString LIKE "%utm_ref%" Then "FaceBook Mobile" Else "None"  END ;;
  }

  dimension: category {
    type: string
    sql: CASE WHEN (${TABLE}.UtmFullString LIKE "%utm_ref%" AND ${utmref} IS NOT NULL) THEN "Referral "
WHEN (((((((${TABLE}.UtmFullString LIKE '%cricbuzz%' OR ${TABLE}.UtmFullString LIKE '%cricinfo%' OR ${TABLE}.UtmFullString LIKE '%google%' OR ${TABLE}.UtmFullString LIKE '%gclid%' OR ${TABLE}.UtmFullString LIKE '%fbads%' OR ${TABLE}.UtmFullString LIKE '%sokfb%' OR ${TABLE}.UtmFullString LIKE '%clickzoot%' OR ${TABLE}.UtmFullString LIKE '%cpa%' OR ${TABLE}.UtmFullString LIKE '%cpc%' OR ${TABLE}.UtmFullString LIKE '%cpr%' OR ${TABLE}.UtmFullString LIKE '%fix%' OR ${TABLE}.UtmFullString LIKE '%cpv%' OR ${TABLE}.UtmFullString LIKE '%cpm%') AND ${TABLE}.UtmFullString IS NOT NULL)) AND ${TABLE}.RegFromWLSId NOT IN (3,5,6,7,8,11,12,13,14,15,16,17,18,19,20,21,22,25,24))) AND ${TABLE}.UtmFullString NOT LIKE '%partner%') AND ${TABLE}.UtmFullString NOT LIKE '%growth%') THEN "Marketing "
WHEN
((((${TABLE}.UtmFullString NOT LIKE '%cricbuzz%' AND ${TABLE}.UtmFullString NOT LIKE '%cricinfo%' AND ${TABLE}.UtmFullString NOT LIKE '%gclid%' AND ${TABLE}.UtmFullString NOT LIKE '%fbads%' AND ${TABLE}.UtmFullString NOT LIKE '%sokfb%' AND ${TABLE}.UtmFullString NOT LIKE '%clickzoot%' AND ${TABLE}.UtmFullString NOT LIKE '%cpa%' AND ${TABLE}.UtmFullString NOT LIKE '%cpc%' AND ${TABLE}.UtmFullString NOT LIKE '%cpr%' AND ${TABLE}.UtmFullString NOT LIKE '%fix%' AND ${TABLE}.UtmFullString NOT LIKE '%cpv%' AND ${TABLE}.UtmFullString NOT LIKE '%cpm%' AND ${TABLE}.UtmFullString NOT LIKE '%utm_ref%' AND ${TABLE}.UtmFullString NOT LIKE '%mysmartprice%' AND ${TABLE}.UtmFullString NOT LIKE '%growth%' AND ${TABLE}.UtmFullString NOT LIKE '%fck%' AND ${TABLE}.UtmFullString NOT LIKE '%social%' AND ${TABLE}.UtmFullString NOT LIKE '%jamie%') AND ${TABLE}.UtmFullString NOT LIKE '%cashkaro%') OR ${TABLE}.UtmFullString IS NULL) AND ${TABLE}.RegFromWLSId NOT IN (3,5,6,7,8,11,12,13,14,15,16,17,18,19,20,21,22,25,24)) THEN "Organic " ELSE "Growth " END ;;
  }

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

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

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

  measure: referer_count {
    type: count_distinct
    filters: [utmref: "NOT NULL"]
    sql: ${utmref} ;;
  }

  measure: Paid_Users_Count {
    label: "Total Paid Users"
    type: count_distinct
    drill_fields: [userid, Email_Id, Mobile, City, Total_EntryFees_Per_User, first_league_join_date]
    sql: ${TABLE}.UserId ;;
  }

  measure: Total_Entry_Fees {
    label: "Total Entry Fees"
    type: sum
    sql: ${TABLE}.TotalEntryFee ;;
  }
}

userstatus.view.lookml · 145 lines

source-preserved

- view: userstatus
  derived_table:
    sql: select entryfees.UserId,reg.EmailId,reg.MobileNum,reg.UtmRef,
reg.Gender,reg.Status,reg.UtmFullString,
reg.updated_from, entryfees.firstleaguedate,
entryfees.lastactivedate, entryfees.TotalEntryFee,
entryfees.LeaguesPlayed,reg.CreatedDate,
reg.IsCommEmailVerified,
reg.IsMobileVerified,
reg.RegFromWLSId,
reg.CurSiteVersion,
reg.City,
entryfees.Amount
from
(SELECT
UserId
,Amount
,MIN(CreatedAt) AS firstleaguedate
,MAX(CreatedAt) As lastactivedate
,SUM(Amount) AS TotalEntryFee
,COUNT(CreatedAt) AS LeaguesPlayed
 FROM ${entry_fees.SQL_TABLE_NAME}
 GROUP BY UserId ORDER BY CreatedAt ASC) as entryfees left join UserRegistration as reg on
 entryfees.UserId=reg.id
  fields:
  - dimension: userid
    type: int
    sql: ${TABLE}.UserId
  - dimension: Email_Id
    sql: ${TABLE}.EmailId
  - dimension: Mobile
    hidden: true
    sql: ${TABLE}.MobileNum
  - dimension: City
    hidden: true
    sql: ${TABLE}.City
  - dimension: Status
    type: yesno
    label: Non Fraud User
    sql: ${TABLE}.Status
  - dimension: Total_EntryFees_Per_User
    type: number
    value_format: '"₹"#.00'
    sql: ${TABLE}.TotalEntryFee
  - dimension: Leagues_Played
    type: number
    sql: ${TABLE}.LeaguesPlayed
  - dimension: firstfees
    type: number
    label: First Entry Fees
    sql: ${TABLE}.Amount
  - dimension: days_since_last_activity
    type: number
    sql: DateDiff(Now(),${TABLE}.lastactivedate)
  - dimension: reg_firstpaid_diff
    type: number
    label: Days Difference (Reg. To FirstPaid)
    sql: DateDiff(${TABLE}.firstleaguedate,${TABLE}.Createddate)
  - dimension: gender
    sql: ${TABLE}.Gender
  - dimension: is_comm_email_verified
    type: yesno
    sql: ${TABLE}.IsCommEmailVerified
  - dimension: is_mobile_verified
    type: yesno
    sql: ${TABLE}.IsMobileVerified
  - dimension: reg_from_wlsid
    type: int
    sql: ${TABLE}.RegFromWLSId
  - dimension: state_id
    type: int
    sql: ${TABLE}.StateId
  - dimension: updated_from
    type: string
    hidden: true
    sql: ${TABLE}.updated_from
  - dimension: site_version
    type: string
    sql: ${TABLE}.CurSiteVersion
  - dimension: utm_fullstring
    sql: ${TABLE}.UtmFullString
  - dimension: utmref
    type: number
    sql: ${TABLE}.UtmRef
  - dimension: Device
    type: string
    sql: CASE WHEN ${TABLE}.Updated_From In ("D","DFC","DGC","DYC","Direct","F","Facebook Connect","Google Connect") THEN "Desktop " ELSE "Mobile " END
  - dimension: Referral_Categories
    type: string
    sql: Case When  ${TABLE}.UtmFullString LIKE '%whatsapp%' Then "WhatsApp" When  ${TABLE}.UtmFullString LIKE '%facebook%' Then "FaceBook" When  ${TABLE}.UtmFullString LIKE '%email%' then  "Email" When ${TABLE}.UtmFullString LIKE '%link%' then "Link" When ${TABLE}.UtmFullString LIKE "%utm_ref%" Then "FaceBook Mobile" Else "None"  END
  - dimension: category
    type: string
    sql: CASE WHEN (${TABLE}.UtmFullString LIKE "%utm_ref%" AND ${utmref} IS NOT NULL) THEN "Referral "
WHEN (((((((${TABLE}.UtmFullString LIKE '%cricbuzz%' OR ${TABLE}.UtmFullString LIKE '%cricinfo%' OR ${TABLE}.UtmFullString LIKE '%google%' OR ${TABLE}.UtmFullString LIKE '%gclid%' OR ${TABLE}.UtmFullString LIKE '%fbads%' OR ${TABLE}.UtmFullString LIKE '%sokfb%' OR ${TABLE}.UtmFullString LIKE '%clickzoot%' OR ${TABLE}.UtmFullString LIKE '%cpa%' OR ${TABLE}.UtmFullString LIKE '%cpc%' OR ${TABLE}.UtmFullString LIKE '%cpr%' OR ${TABLE}.UtmFullString LIKE '%fix%' OR ${TABLE}.UtmFullString LIKE '%cpv%' OR ${TABLE}.UtmFullString LIKE '%cpm%') AND ${TABLE}.UtmFullString IS NOT NULL)) AND ${TABLE}.RegFromWLSId NOT IN (3,5,6,7,8,11,12,13,14,15,16,17,18,19,20,21,22,25,24))) AND ${TABLE}.UtmFullString NOT LIKE '%partner%') AND ${TABLE}.UtmFullString NOT LIKE '%growth%') THEN "Marketing "
WHEN
((((${TABLE}.UtmFullString NOT LIKE '%cricbuzz%' AND ${TABLE}.UtmFullString NOT LIKE '%cricinfo%' AND ${TABLE}.UtmFullString NOT LIKE '%gclid%' AND ${TABLE}.UtmFullString NOT LIKE '%fbads%' AND ${TABLE}.UtmFullString NOT LIKE '%sokfb%' AND ${TABLE}.UtmFullString NOT LIKE '%clickzoot%' AND ${TABLE}.UtmFullString NOT LIKE '%cpa%' AND ${TABLE}.UtmFullString NOT LIKE '%cpc%' AND ${TABLE}.UtmFullString NOT LIKE '%cpr%' AND ${TABLE}.UtmFullString NOT LIKE '%fix%' AND ${TABLE}.UtmFullString NOT LIKE '%cpv%' AND ${TABLE}.UtmFullString NOT LIKE '%cpm%' AND ${TABLE}.UtmFullString NOT LIKE '%utm_ref%' AND ${TABLE}.UtmFullString NOT LIKE '%mysmartprice%' AND ${TABLE}.UtmFullString NOT LIKE '%growth%' AND ${TABLE}.UtmFullString NOT LIKE '%fck%' AND ${TABLE}.UtmFullString NOT LIKE '%social%' AND ${TABLE}.UtmFullString NOT LIKE '%jamie%') AND ${TABLE}.UtmFullString NOT LIKE '%cashkaro%') OR ${TABLE}.UtmFullString IS NULL) AND ${TABLE}.RegFromWLSId NOT IN (3,5,6,7,8,11,12,13,14,15,16,17,18,19,20,21,22,25,24)) THEN "Organic " ELSE "Growth " END
  - dimension_group: first_league_join_date
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    - time
    datatype: datetime
    sql: ${TABLE}.firstleaguedate
  - dimension_group: last_active_date
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    - time
    datatype: datetime
    sql: ${TABLE}.lastactivedate
  - dimension_group: date_of_registration
    type: time
    timeframes:
    - time
    - date
    - week
    - year
    - month
    sql: ${TABLE}.Createddate
  - measure: referer_count
    type: count_distinct
    filters:
    - utmref: NOT NULL
    sql: ${utmref}
  - measure: Paid_Users_Count
    type: count_distinct
    label: Total Paid Users
    drill_fields:
    - userid
    - Email_Id
    - Mobile
    - City
    - Total_EntryFees_Per_User
    - first_league_join_date
    sql: ${TABLE}.UserId
  - measure: Total_Entry_Fees
    type: sum
    label: Total Entry Fees
    sql: ${TABLE}.TotalEntryFee

userstatus.sql
userstatus.sql
userstatus.view.lkml
userstatus.view.lookml

Userstatus

Blocks

Assumptions

Contents