SQL Nutrition coaching

User Activity Facts

User Activity Facts model for nutrition coaching.

source-preserved

Blocks

dbt SQL LKML LookML

user_activity_facts.sql · 80 lines

inferred

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

SELECT m.client_id AS client_id
  , mc.timestamp::DATE AS activity_date
  , 'Comment'::TEXT AS event_type
  , 'User_Role'::TEXT AS creator
  , CONCAT(mc.timestamp::DATE, m.client_id) as date_client_id
FROM meal_comment AS mc
  LEFT JOIN meal as m ON m.id = mc.meal_id
WHERE m.client_id = mc.user_id
AND {% condition activity_date %} mc.timestamp::DATE {% endcondition %}
AND {% condition client_id %} m.client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL
SELECT client_id AS client_id
  , timestamp::DATE AS activity_date
  , 'Message'::TEXT AS event_type
  , 'User_Role'::TEXT AS creator
  , CONCAT(timestamp::DATE, client_id) as date_client_id
FROM message
WHERE client_id = created_by
AND {% condition activity_date %} timestamp::DATE {% endcondition %}
AND {% condition client_id %} client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL

SELECT m.client_id AS client_id
  , me.created_at::DATE AS activity_date
  , 'Meal Entry'::TEXT AS event_type
  , 'User_Role'::TEXT AS creator
  , CONCAT(me.created_at::DATE, client_id) as date_client_id
FROM meal_entry AS me
LEFT JOIN meal AS m ON m.id = me.meal_id
WHERE {% condition activity_date %} me.created_at::DATE {% endcondition %}
AND {% condition client_id %} m.client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL

SELECT m.client_id AS client_id
  , mc.timestamp::DATE AS activity_date
  , 'Comment'::TEXT AS event_type
  , 'Coach'::TEXT AS creator
  , CONCAT(mc.timestamp::DATE, m.client_id) as date_client_id
FROM meal_comment AS mc
  LEFT JOIN meal as m ON m.id = mc.meal_id
WHERE m.client_id <> mc.user_id
AND {% condition activity_date %} mc.timestamp::DATE {% endcondition %}
AND {% condition client_id %} m.client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL

SELECT client_id AS client_id
  , timestamp::DATE AS activity_date
  , 'Message'::TEXT AS event_type
  , 'Coach'::TEXT AS creator
  , CONCAT(timestamp::DATE, client_id) as date_client_id
FROM message
WHERE client_id <> created_by
AND {% condition activity_date %} timestamp::DATE {% endcondition %}
AND {% condition client_id %} client_id {% endcondition %}
-- GROUP BY 1,2

# models/schema.yml
version: 2

models:
  - name: user_activity_facts
    columns:
      - name: client_id
        tests:
          - unique
          - not_null
      - name: event_type
      - name: creator
      - name: date_client_id

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.

user_activity_facts.sql · 63 lines

source-preserved validation: not checked

SELECT m.client_id AS client_id
  , mc.timestamp::DATE AS activity_date
  , 'Comment'::TEXT AS event_type
  , 'User_Role'::TEXT AS creator
  , CONCAT(mc.timestamp::DATE, m.client_id) as date_client_id
FROM meal_comment AS mc
  LEFT JOIN meal as m ON m.id = mc.meal_id
WHERE m.client_id = mc.user_id
AND {% condition activity_date %} mc.timestamp::DATE {% endcondition %}
AND {% condition client_id %} m.client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL
SELECT client_id AS client_id
  , timestamp::DATE AS activity_date
  , 'Message'::TEXT AS event_type
  , 'User_Role'::TEXT AS creator
  , CONCAT(timestamp::DATE, client_id) as date_client_id
FROM message
WHERE client_id = created_by
AND {% condition activity_date %} timestamp::DATE {% endcondition %}
AND {% condition client_id %} client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL

SELECT m.client_id AS client_id
  , me.created_at::DATE AS activity_date
  , 'Meal Entry'::TEXT AS event_type
  , 'User_Role'::TEXT AS creator
  , CONCAT(me.created_at::DATE, client_id) as date_client_id
FROM meal_entry AS me
LEFT JOIN meal AS m ON m.id = me.meal_id
WHERE {% condition activity_date %} me.created_at::DATE {% endcondition %}
AND {% condition client_id %} m.client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL

SELECT m.client_id AS client_id
  , mc.timestamp::DATE AS activity_date
  , 'Comment'::TEXT AS event_type
  , 'Coach'::TEXT AS creator
  , CONCAT(mc.timestamp::DATE, m.client_id) as date_client_id
FROM meal_comment AS mc
  LEFT JOIN meal as m ON m.id = mc.meal_id
WHERE m.client_id <> mc.user_id
AND {% condition activity_date %} mc.timestamp::DATE {% endcondition %}
AND {% condition client_id %} m.client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL

SELECT client_id AS client_id
  , timestamp::DATE AS activity_date
  , 'Message'::TEXT AS event_type
  , 'Coach'::TEXT AS creator
  , CONCAT(timestamp::DATE, client_id) as date_client_id
FROM message
WHERE client_id <> created_by
AND {% condition activity_date %} timestamp::DATE {% endcondition %}
AND {% condition client_id %} client_id {% endcondition %}
-- GROUP BY 1,2

user_activity_facts.view.lkml · 145 lines

inferred

view: user_activity_facts {
  derived_table: {
    sql: SELECT m.client_id AS client_id
  , mc.timestamp::DATE AS activity_date
  , 'Comment'::TEXT AS event_type
  , 'User_Role'::TEXT AS creator
  , CONCAT(mc.timestamp::DATE, m.client_id) as date_client_id
FROM meal_comment AS mc
  LEFT JOIN meal as m ON m.id = mc.meal_id
WHERE m.client_id = mc.user_id
AND {% condition activity_date %} mc.timestamp::DATE {% endcondition %}
AND {% condition client_id %} m.client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL
SELECT client_id AS client_id
  , timestamp::DATE AS activity_date
  , 'Message'::TEXT AS event_type
  , 'User_Role'::TEXT AS creator
  , CONCAT(timestamp::DATE, client_id) as date_client_id
FROM message
WHERE client_id = created_by
AND {% condition activity_date %} timestamp::DATE {% endcondition %}
AND {% condition client_id %} client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL

SELECT m.client_id AS client_id
  , me.created_at::DATE AS activity_date
  , 'Meal Entry'::TEXT AS event_type
  , 'User_Role'::TEXT AS creator
  , CONCAT(me.created_at::DATE, client_id) as date_client_id
FROM meal_entry AS me
LEFT JOIN meal AS m ON m.id = me.meal_id
WHERE {% condition activity_date %} me.created_at::DATE {% endcondition %}
AND {% condition client_id %} m.client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL

SELECT m.client_id AS client_id
  , mc.timestamp::DATE AS activity_date
  , 'Comment'::TEXT AS event_type
  , 'Coach'::TEXT AS creator
  , CONCAT(mc.timestamp::DATE, m.client_id) as date_client_id
FROM meal_comment AS mc
  LEFT JOIN meal as m ON m.id = mc.meal_id
WHERE m.client_id <> mc.user_id
AND {% condition activity_date %} mc.timestamp::DATE {% endcondition %}
AND {% condition client_id %} m.client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL

SELECT client_id AS client_id
  , timestamp::DATE AS activity_date
  , 'Message'::TEXT AS event_type
  , 'Coach'::TEXT AS creator
  , CONCAT(timestamp::DATE, client_id) as date_client_id
FROM message
WHERE client_id <> created_by
AND {% condition activity_date %} timestamp::DATE {% endcondition %}
AND {% condition client_id %} client_id {% endcondition %}
-- GROUP BY 1,2 ;;
  }

  dimension: client_id {
    primary_key: yes
    sql: ${TABLE}.client_id ;;
  }

  dimension: event_type {
    sql: ${TABLE}.event_type ;;
  }

  dimension: creator {
    sql: ${TABLE}.creator ;;
  }

  dimension: date_client_id {
    sql: ${TABLE}.date_client_id ;;
  }

  dimension_group: activity {
    type: time
    timeframes: [date, month]
    sql: ${TABLE}.activity_date ;;
  }

  measure: count_meals_entries {
    type: count
    filters: [event_type: "Meal_Entry"]
  }

  measure: count_distinct_meal_entries {
    type: count_distinct
    filters: [event_type: "Meal_Entry"]
    sql: ${TABLE}.date_client_id ;;
  }

  measure: count_comments_posted {
    type: count
    filters: [event_type: "Comment"]
    filters: [creator: "Client"]
  }

  measure: count_comments_recieved {
    type: count
    filters: [event_type: "Comment"]
    filters: [creator: "Coach"]
  }

  measure: count_messages_posted {
    type: count
    filters: [event_type: "Messages"]
    filters: [creator: "Client"]
  }

  measure: count_messages_recieved {
    type: count
    filters: [event_type: "Message"]
    filters: [creator: "Coach"]
  }

  measure: count_distinct_day_client {
    label: "Client Activity"
    type: count_distinct
    filters: [creator: "Client"]
    sql: ${TABLE}.date_client_id ;;
  }

  measure: count_distinct_day_coach {
    label: "Coach Activity"
    type: count_distinct
    filters: [creator: "Coach"]
    sql: ${TABLE}.date_client_id ;;
  }

  measure: activity_ratio {
    type: number
    drill_fields: [detail*]
    sql: 1.0 * ${count_distinct_day_coach} / NULLIF(${count_distinct_day_client},0) ;;
  }
}

user_activity_facts.view.lookml · 127 lines

source-preserved

- view: user_activity_facts
  derived_table:
    sql: SELECT m.client_id AS client_id
  , mc.timestamp::DATE AS activity_date
  , 'Comment'::TEXT AS event_type
  , 'User_Role'::TEXT AS creator
  , CONCAT(mc.timestamp::DATE, m.client_id) as date_client_id
FROM meal_comment AS mc
  LEFT JOIN meal as m ON m.id = mc.meal_id
WHERE m.client_id = mc.user_id
AND {% condition activity_date %} mc.timestamp::DATE {% endcondition %}
AND {% condition client_id %} m.client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL
SELECT client_id AS client_id
  , timestamp::DATE AS activity_date
  , 'Message'::TEXT AS event_type
  , 'User_Role'::TEXT AS creator
  , CONCAT(timestamp::DATE, client_id) as date_client_id
FROM message
WHERE client_id = created_by
AND {% condition activity_date %} timestamp::DATE {% endcondition %}
AND {% condition client_id %} client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL

SELECT m.client_id AS client_id
  , me.created_at::DATE AS activity_date
  , 'Meal Entry'::TEXT AS event_type
  , 'User_Role'::TEXT AS creator
  , CONCAT(me.created_at::DATE, client_id) as date_client_id
FROM meal_entry AS me
LEFT JOIN meal AS m ON m.id = me.meal_id
WHERE {% condition activity_date %} me.created_at::DATE {% endcondition %}
AND {% condition client_id %} m.client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL

SELECT m.client_id AS client_id
  , mc.timestamp::DATE AS activity_date
  , 'Comment'::TEXT AS event_type
  , 'Coach'::TEXT AS creator
  , CONCAT(mc.timestamp::DATE, m.client_id) as date_client_id
FROM meal_comment AS mc
  LEFT JOIN meal as m ON m.id = mc.meal_id
WHERE m.client_id <> mc.user_id
AND {% condition activity_date %} mc.timestamp::DATE {% endcondition %}
AND {% condition client_id %} m.client_id {% endcondition %}
-- GROUP BY 1,2

UNION ALL

SELECT client_id AS client_id
  , timestamp::DATE AS activity_date
  , 'Message'::TEXT AS event_type
  , 'Coach'::TEXT AS creator
  , CONCAT(timestamp::DATE, client_id) as date_client_id
FROM message
WHERE client_id <> created_by
AND {% condition activity_date %} timestamp::DATE {% endcondition %}
AND {% condition client_id %} client_id {% endcondition %}
-- GROUP BY 1,2
  fields:
  - dimension: client_id
    primary_key: true
    sql: ${TABLE}.client_id
  - dimension: event_type
    sql: ${TABLE}.event_type
  - dimension: creator
    sql: ${TABLE}.creator
  - dimension: date_client_id
    sql: ${TABLE}.date_client_id
  - dimension_group: activity
    type: time
    timeframes:
    - date
    - month
    sql: ${TABLE}.activity_date
  - measure: count_meals_entries
    type: count
    filters:
    - event_type: Meal_Entry
  - measure: count_distinct_meal_entries
    type: count_distinct
    filters:
    - event_type: Meal_Entry
    sql: ${TABLE}.date_client_id
  - measure: count_comments_posted
    type: count
    filters:
    - event_type: Comment
    - creator: Client
  - measure: count_comments_recieved
    type: count
    filters:
    - event_type: Comment
    - creator: Coach
  - measure: count_messages_posted
    type: count
    filters:
    - event_type: Messages
    - creator: Client
  - measure: count_messages_recieved
    type: count
    filters:
    - event_type: Message
    - creator: Coach
  - measure: count_distinct_day_client
    type: count_distinct
    label: Client Activity
    filters:
    - creator: Client
    sql: ${TABLE}.date_client_id
  - measure: count_distinct_day_coach
    type: count_distinct
    label: Coach Activity
    filters:
    - creator: Coach
    sql: ${TABLE}.date_client_id
  - measure: activity_ratio
    type: number
    drill_fields:
    - detail*
    sql: 1.0 * ${count_distinct_day_coach} / NULLIF(${count_distinct_day_client},0)

user_activity_facts.sql
user_activity_facts.sql
user_activity_facts.view.lkml
user_activity_facts.view.lookml

User Activity Facts

Blocks

Assumptions

Contents