LookML to Generate Example Looker Model

survival.model.lookml

# preliminaries #

- connection: [your_database_connection]
- scoping: true
- case_sensitive: false


# views to explore #

- explore: payments
  joins:
    - join: users
      sql_on: ${payments.user_id} = ${users.id}
    
    - join: user_facts
      sql_on: ${payments.user_id} = ${user_facts.user_id}
      
      
# payments view definition #      

- view: payments
  sql_table_name: survival.orders
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
    
  - dimension: user_id
    type: int
    hidden: true
    sql: ${TABLE}.user_id

  - dimension_group: created
    type: time
    timeframes: [month, raw]
    sql: ${TABLE}.created_month
    
  - dimension: months_since_signup
    type: int
    sql: datediff(month, ${users.signup_raw}, ${created_raw})

  - dimension: subscription_amount
    type: number
    sql: ${TABLE}.subscription_amount  

  - measure: number_of_payments
    type: count
    
    
# user view definition #

- view: users
  sql_table_name: survival.users
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.user_id
  
  - dimension_group: signup
    type: time
    timeframes: [month, raw]
    sql: ${TABLE}.signup_date   
    
  - dimension: months_a_customer
    type: int
    sql: datediff(month, ${signup_raw}, ${user_facts.most_recent_payment_raw})        
  
  - measure: number_of_users
    type: count
    
    
# user-facts view definition #

- view: user_facts
  derived_table:
    sql: |
      select user_id
        , max(created_month) as most_recent_payment
      from survival.orders as user_facts
      group by 1
  fields:
    - dimension: user_id
      type: int
      primary_key: true
      hidden: true
      sql: ${TABLE}.user_id
    
    - dimension_group: most_recent_payment
      type: time
      timeframes: [month, raw]
      sql: ${TABLE}.most_recent_payment