Idle Time

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

WITH sessions as(
SELECT session.session_id, session.car_id, session.session_start_time, session.session_end_time,
sum(IFNULL("user".usr_id,0)) as user_list,
rank() over (partition by session.car_id order by session.session_id) as rank
FROM unite_db_stage.session AS session
INNER JOIN unite_db_stage.usr_in_session AS user_in_session ON session.session_id=user_in_session.session_id
INNER JOIN unite_db_stage.usr AS "user" ON ("user".usr_id)=user_in_session.usr_id
where session_type=1
group by session.session_id, session.session_start_time, session.session_end_time, session.car_id
)

select s1.session_id as session_id, s1.car_id as car_id,
s2.session_end_time as idle_session_start,
s1.session_start_time as idle_session_end
from sessions s1
inner join sessions s2 on s1.car_id=s2.car_id and s2.rank=s1.rank-1
where s2.session_end_time<>s1.session_start_time
and s1.user_list<>s2.user_list

# models/schema.yml
version: 2

models:
  - name: idle_time
    columns:
      - name: session_id
        tests:
          - unique
          - not_null
      - name: car_id
      - name: idle_minutes

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.

WITH sessions as(
SELECT session.session_id, session.car_id, session.session_start_time, session.session_end_time,
sum(IFNULL("user".usr_id,0)) as user_list,
rank() over (partition by session.car_id order by session.session_id) as rank
FROM unite_db_stage.session AS session
INNER JOIN unite_db_stage.usr_in_session AS user_in_session ON session.session_id=user_in_session.session_id
INNER JOIN unite_db_stage.usr AS "user" ON ("user".usr_id)=user_in_session.usr_id
where session_type=1
group by session.session_id, session.session_start_time, session.session_end_time, session.car_id
)

select s1.session_id as session_id, s1.car_id as car_id,
s2.session_end_time as idle_session_start,
s1.session_start_time as idle_session_end
from sessions s1
inner join sessions s2 on s1.car_id=s2.car_id and s2.rank=s1.rank-1
where s2.session_end_time<>s1.session_start_time
and s1.user_list<>s2.user_list

view: idle_time {
  derived_table: {
    sql: WITH sessions as(
SELECT session.session_id, session.car_id, session.session_start_time, session.session_end_time,
sum(IFNULL("user".usr_id,0)) as user_list,
rank() over (partition by session.car_id order by session.session_id) as rank
FROM unite_db_stage.session AS session
INNER JOIN unite_db_stage.usr_in_session AS user_in_session ON session.session_id=user_in_session.session_id
INNER JOIN unite_db_stage.usr AS "user" ON ("user".usr_id)=user_in_session.usr_id
where session_type=1
group by session.session_id, session.session_start_time, session.session_end_time, session.car_id
)

select s1.session_id as session_id, s1.car_id as car_id,
s2.session_end_time as idle_session_start,
s1.session_start_time as idle_session_end
from sessions s1
inner join sessions s2 on s1.car_id=s2.car_id and s2.rank=s1.rank-1
where s2.session_end_time<>s1.session_start_time
and s1.user_list<>s2.user_list ;;
  }

  dimension: session_id {
    type: number
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.session_id ;;
  }

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

  dimension: idle_minutes {
    sql: TIMESTAMPDIFF(SQL_TSI_MINUTE,${TABLE}.idle_session_start,${TABLE}.idle_session_end) ;;
  }

  dimension_group: idle_start {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.idle_session_start ;;
  }

  dimension_group: idle_end {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.idle_session_end ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: total_idle_minutes {
    type: sum
    sql: ${idle_minutes} ;;
  }
}

- view: idle_time
  derived_table:
    sql: WITH sessions as(
SELECT session.session_id, session.car_id, session.session_start_time, session.session_end_time,
sum(IFNULL("user".usr_id,0)) as user_list,
rank() over (partition by session.car_id order by session.session_id) as rank
FROM unite_db_stage.session AS session
INNER JOIN unite_db_stage.usr_in_session AS user_in_session ON session.session_id=user_in_session.session_id
INNER JOIN unite_db_stage.usr AS "user" ON ("user".usr_id)=user_in_session.usr_id
where session_type=1
group by session.session_id, session.session_start_time, session.session_end_time, session.car_id
)

select s1.session_id as session_id, s1.car_id as car_id,
s2.session_end_time as idle_session_start,
s1.session_start_time as idle_session_end
from sessions s1
inner join sessions s2 on s1.car_id=s2.car_id and s2.rank=s1.rank-1
where s2.session_end_time<>s1.session_start_time
and s1.user_list<>s2.user_list
  fields:
  - dimension: session_id
    type: number
    primary_key: true
    hidden: true
    sql: ${TABLE}.session_id
  - dimension: car_id
    type: number
    sql: ${TABLE}.car_id
  - dimension: idle_minutes
    sql: TIMESTAMPDIFF(SQL_TSI_MINUTE,${TABLE}.idle_session_start,${TABLE}.idle_session_end)
  - dimension_group: idle_start
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.idle_session_start
  - dimension_group: idle_end
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.idle_session_end
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: total_idle_minutes
    type: sum
    sql: ${idle_minutes}