Deposit Userstatus

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

select deposits.UserId,reg.EmailId,reg.UtmRef
,reg.Status,reg.UtmFullString,
deposits.firstdepositdate,
deposits.lastdepositdate, deposits.totaldeposits,
reg.CreatedDate,
reg.RegFromWLSId,
deposits.Amount
from
(
SELECT
UserId,
Amount
, MIN(CreatedAt) AS firstdepositdate
, MAX(CreatedAt) AS lastdepositdate
,SUM(Amount) AS totaldeposits
, COUNT(CreatedAt) AS deposits_count
FROM {{ ref('deposits') }}
GROUP BY UserId ORDER BY CreatedAt ASC) as  deposits left join UserRegistration as reg on
deposits.UserId=reg.id

# models/schema.yml
version: 2

models:
  - name: deposit_userstatus
    columns:
      - name: userid
      - name: Email_Id
      - name: Status
      - name: Total_deposits
      - name: firstdepositamount
      - name: reg_firstdeposit_diff
      - name: firstdeposit_tier
      - name: deposits_count
      - name: reg_from_wlsid
      - 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.

select deposits.UserId,reg.EmailId,reg.UtmRef
,reg.Status,reg.UtmFullString,
deposits.firstdepositdate,
deposits.lastdepositdate, deposits.totaldeposits,
reg.CreatedDate,
reg.RegFromWLSId,
deposits.Amount
from
(
SELECT
UserId,
Amount
, MIN(CreatedAt) AS firstdepositdate
, MAX(CreatedAt) AS lastdepositdate
,SUM(Amount) AS totaldeposits
, COUNT(CreatedAt) AS deposits_count
FROM ${deposits.SQL_TABLE_NAME}
GROUP BY UserId ORDER BY CreatedAt ASC) as  deposits left join UserRegistration as reg on
deposits.UserId=reg.id

view: deposit_userstatus {
  derived_table: {
    sql: select deposits.UserId,reg.EmailId,reg.UtmRef
,reg.Status,reg.UtmFullString,
deposits.firstdepositdate,
deposits.lastdepositdate, deposits.totaldeposits,
reg.CreatedDate,
reg.RegFromWLSId,
deposits.Amount
from
(
SELECT
UserId,
Amount
, MIN(CreatedAt) AS firstdepositdate
, MAX(CreatedAt) AS lastdepositdate
,SUM(Amount) AS totaldeposits
, COUNT(CreatedAt) AS deposits_count
FROM ${deposits.SQL_TABLE_NAME}
GROUP BY UserId ORDER BY CreatedAt ASC) as  deposits left join UserRegistration as reg on
deposits.UserId=reg.id ;;
  }

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

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

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

  dimension: Total_deposits {
    label: "Total Deposits by User"
    type: number
    value_format: "\"₹\"#.00"
    sql: ${TABLE}.totaldeposits ;;
  }

  dimension: firstdepositamount {
    type: number
    hidden: yes
    sql: ${TABLE}.Amount ;;
  }

  dimension: reg_firstdeposit_diff {
    label: "Days Difference (Reg. To First deposit)"
    type: number
    sql: DateDiff(${TABLE}.firstdepositdate,${TABLE}.Createddate) ;;
  }

  dimension: firstdeposit_tier {
    label: "First Deposit Category (Small/medium/large amount)"
    type: tier
    sql: ${firstdepositamount} ;;
  }

  dimension: deposits_count {
    label: "Users Deposit Count"
    type: number
    sql: ${TABLE}.deposits_count ;;
  }

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

  dimension: category {
    label: "Category (Marketing/Organic/Referral/Growth)"
    type: string
    sql: CASE WHEN (${TABLE}.UtmFullString LIKE "%utm_ref%" AND ${TABLE}.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_deposit_date {
    label: "First Deposit Date"
    type: time
    timeframes: [date, week, month, year, time]
    datatype: datetime
    sql: ${TABLE}.firstdepositdate ;;
  }

  dimension_group: last_deposit_date {
    label: "Last Deposit Date"
    type: time
    timeframes: [date, week, month, year, time]
    datatype: datetime
    sql: ${TABLE}.lastdepositdate ;;
  }

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

  measure: Paid_Users_Count {
    label: "Total Depositors"
    type: count_distinct
    sql: ${TABLE}.UserId ;;
  }
}

- view: deposit_userstatus
  derived_table:
    sql: select deposits.UserId,reg.EmailId,reg.UtmRef
,reg.Status,reg.UtmFullString,
deposits.firstdepositdate,
deposits.lastdepositdate, deposits.totaldeposits,
reg.CreatedDate,
reg.RegFromWLSId,
deposits.Amount
from
(
SELECT
UserId,
Amount
, MIN(CreatedAt) AS firstdepositdate
, MAX(CreatedAt) AS lastdepositdate
,SUM(Amount) AS totaldeposits
, COUNT(CreatedAt) AS deposits_count
FROM ${deposits.SQL_TABLE_NAME}
GROUP BY UserId ORDER BY CreatedAt ASC) as  deposits left join UserRegistration as reg on
deposits.UserId=reg.id
  fields:
  - dimension: userid
    type: int
    sql: ${TABLE}.UserId
  - dimension: Email_Id
    sql: ${TABLE}.EmailId
  - dimension: Status
    type: yesno
    label: Non Fraud User
    sql: ${TABLE}.Status
  - dimension: Total_deposits
    type: number
    label: Total Deposits by User
    value_format: '"₹"#.00'
    sql: ${TABLE}.totaldeposits
  - dimension: firstdepositamount
    type: number
    hidden: true
    sql: ${TABLE}.Amount
  - dimension: reg_firstdeposit_diff
    type: number
    label: Days Difference (Reg. To First deposit)
    sql: DateDiff(${TABLE}.firstdepositdate,${TABLE}.Createddate)
  - dimension: firstdeposit_tier
    type: tier
    label: First Deposit Category (Small/medium/large amount)
    sql: ${firstdepositamount}
  - dimension: deposits_count
    type: number
    label: Users Deposit Count
    sql: ${TABLE}.deposits_count
  - dimension: reg_from_wlsid
    type: int
    sql: ${TABLE}.RegFromWLSId
  - dimension: category
    type: string
    label: Category (Marketing/Organic/Referral/Growth)
    sql: CASE WHEN (${TABLE}.UtmFullString LIKE "%utm_ref%" AND ${TABLE}.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_deposit_date
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    - time
    datatype: datetime
    sql: ${TABLE}.firstdepositdate
  - dimension_group: last_deposit_date
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    - time
    datatype: datetime
    sql: ${TABLE}.lastdepositdate
  - dimension_group: date_of_registration
    type: time
    timeframes:
    - time
    - date
    - week
    - year
    - month
    sql: ${TABLE}.Createddate
  - measure: Paid_Users_Count
    type: count_distinct
    label: Total Depositors
    sql: ${TABLE}.UserId