Daily Users Activity

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

SELECT general_date.date AS general_date
  , c.id AS customer_id
  , c.entry_time::DATE
  , c.canceled_date::DATE
  , general_date.date - c.entry_time::DATE AS days_a_customer
FROM (SELECT GENERATE_SERIES(<% start_date_placeholder %>, CURRENT_DATE, '1 day')::DATE AS date) AS general_date
LEFT JOIN members AS c
ON c.entry_time::DATE < general_date.date
AND (CASE
      WHEN c.canceled_date::DATE IS NOT NULL
      THEN c.canceled_date::DATE
      ELSE CURRENT_DATE::DATE
    END) >= general_date.date
AND ((membership_start_time IS NOT NULL)
    OR (membership_start_time IS NULL AND (c.status = 0 OR (c.canceled_date::DATE IS NULL AND c.status <> 1)))
    )

# models/schema.yml
version: 2

models:
  - name: daily_users_activity
    columns:
      - name: active
      - name: customer_id
      - name: created
      - name: canceled
      - name: days_a_customer

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 general_date.date AS general_date
  , c.id AS customer_id
  , c.entry_time::DATE
  , c.canceled_date::DATE
  , general_date.date - c.entry_time::DATE AS days_a_customer
FROM (SELECT GENERATE_SERIES(<% start_date_placeholder %>, CURRENT_DATE, '1 day')::DATE AS date) AS general_date
LEFT JOIN members AS c
ON c.entry_time::DATE < general_date.date
AND (CASE
      WHEN c.canceled_date::DATE IS NOT NULL
      THEN c.canceled_date::DATE
      ELSE CURRENT_DATE::DATE
    END) >= general_date.date
AND ((membership_start_time IS NOT NULL)
    OR (membership_start_time IS NULL AND (c.status = 0 OR (c.canceled_date::DATE IS NULL AND c.status <> 1)))
    )

view: daily_users_activity {
  derived_table: {
    sql: SELECT general_date.date AS general_date
  , c.id AS customer_id
  , c.entry_time::DATE
  , c.canceled_date::DATE
  , general_date.date - c.entry_time::DATE AS days_a_customer
FROM (SELECT GENERATE_SERIES(<% start_date_placeholder %>, CURRENT_DATE, '1 day')::DATE AS date) AS general_date
LEFT JOIN members AS c
ON c.entry_time::DATE < general_date.date
AND (CASE
      WHEN c.canceled_date::DATE IS NOT NULL
      THEN c.canceled_date::DATE
      ELSE CURRENT_DATE::DATE
    END) >= general_date.date
AND ((membership_start_time IS NOT NULL)
    OR (membership_start_time IS NULL AND (c.status = 0 OR (c.canceled_date::DATE IS NULL AND c.status <> 1)))
    ) ;;
  }

  dimension: active {
    type: time
    sql: ${TABLE}.general_date ;;
  }

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

  dimension: created {
    type: time
    sql: ${TABLE}.created_at ;;
  }

  dimension: canceled {
    type: time
    sql: ${TABLE}.canceled_date ;;
  }

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

  measure: count_users {
    type: count_distinct
    sql: ${customer_id} ;;
  }
}

- view: daily_users_activity
  derived_table:
    sql: SELECT general_date.date AS general_date
  , c.id AS customer_id
  , c.entry_time::DATE
  , c.canceled_date::DATE
  , general_date.date - c.entry_time::DATE AS days_a_customer
FROM (SELECT GENERATE_SERIES(<% start_date_placeholder %>, CURRENT_DATE, '1 day')::DATE AS date) AS general_date
LEFT JOIN members AS c
ON c.entry_time::DATE < general_date.date
AND (CASE
      WHEN c.canceled_date::DATE IS NOT NULL
      THEN c.canceled_date::DATE
      ELSE CURRENT_DATE::DATE
    END) >= general_date.date
AND ((membership_start_time IS NOT NULL)
    OR (membership_start_time IS NULL AND (c.status = 0 OR (c.canceled_date::DATE IS NULL AND c.status <> 1)))
    )
  fields:
  - dimension: active
    type: time
    sql: ${TABLE}.general_date
  - dimension: customer_id
    type: int
    sql: ${TABLE}.customer_id
  - dimension: created
    type: time
    sql: ${TABLE}.created_at
  - dimension: canceled
    type: time
    sql: ${TABLE}.canceled_date
  - dimension: days_a_customer
    type: number
    sql: ${TABLE}.days_a_customer
  - measure: count_users
    type: count_distinct
    sql: ${customer_id}