stream_sql.yml

source:
  name: ApplicationEvents
  type: HTTP # creates an HTTP POST endpoint to ingest events
  format: JSON
  schema:
    - name: type
      type: VARCHAR
    - name: userId
      type: VARCHAR
    - name: score
      type: INT
    - name: amountPurchased
      type: INT
    - name: timestamp
      type: DATE
      format: 'yyyy-MM-dd'T'HH:mm:ss'

stream: 
  name: UserSessions
  query: >
    SELECT userId, 
           sum(amountPurchased) as amountPurchased,
           last(score) - first(score) as progress
           timestampdiff(last(timestamp),first(timestamp), SECONDS) duration_seconds
           session_start(timestamp) as startTimestamp
    FROM ApplicationEvents
    GROUP BY SESSION(timestamp, INTERVAL '10' MINUTE), userId   

stream: 
  name: RetentionDay1
  query: >
    SELECT userId, 
           count(*) as session_count
    FROM UserSessions
    GROUP BY TUMBLE(startTimestamp, INTERVAL '24' HOUR), userId 

stream: 
  name: RetentionDay3
  query: >
    SELECT userId, 
           sum(session_count) as session_count
    FROM RetentionDay1
    GROUP BY TUMBLE(startTimestamp, INTERVAL '3' DAY), userId    

stream: 
  name: RetentionDay7
  query: >
    SELECT userId, 
           sum(session_count) as session_count
    FROM RetentionDay1
    GROUP BY TUMBLE(startTimestamp, INTERVAL '7' DAY), userId  

stream: 
  name: RetentionDay30
  query: >
    SELECT userId, 
           sum(session_count) as session_count
    FROM RetentionDay3
    GROUP BY TUMBLE(startTimestamp, INTERVAL '30' DAY), userId           


table: # data is stored in a PostgreSQL table accessed by any tool
  name: Retention
  indices:
    - userId
  query: >
    SELECT r1.userId, 
           count(r1.session_count) > 0 as active1Day,
           count(r3.session_count) > 0 as active3Day,
           count(r7.session_count) > 0 as active7Day,
           count(r30.session_count) > 0 as active30Day,
           now() as timestamp
    FROM RetentionDay1 as r1, 
         RetentionDay3 as r3,
         RetentionDay7 as r7, 
         RetentionDay30 as r30
    WHERE r3.userId = r1.userId
      AND r7.userId = r1.userId    
      AND r30.userId = r1.userId