SQL
Delivery service
Driver Aggregate Worked Hod
Driver Aggregate Worked Hod model for delivery service.
source-preserved
Blocks
driver_aggregate_worked_hod.sql · 77 lines
inferred
-- models/driver_aggregate_worked_hod.sql
{{ config(materialized='view') }}
Select
metros_name
,Active_Date_Local
,Active_HoD
,count(distinct user_id) as Working_Driver_Count
from
(Select user_id
,case when metros.name IN ('Houston East','Houston Northwest','Houston Southwest','Houston') THEN 'Houston'
else metros.name
end metros_name
,DATE(case
when metros.id in (24) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.actual_start_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.actual_start_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Eastern')
end) Active_Date_Local
,extract(hour from
case when metros.id in (24) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.actual_start_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.actual_start_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Eastern')
end) Active_HoD
From tasks
JOIN shipment_companyeries
ON tasks.taskable_id=shipment_companyeries.id
JOIN orders
ON shipment_companyeries.order_id = orders.id
JOIN metros
ON orders.metro_id=metros.id
Where tasks.status NOT IN ('cancelled', 'canceled')
And shipment_companyeries.type <> 'TestDeliveryCompanyery'
and DATE(tasks.actual_start_at) >= <% parameter start_date_placeholder %>
Group By 1,2,3,4
UNION
Select user_id
,case when metros.name IN ('Houston East','Houston Northwest','Houston Southwest','Houston') THEN 'Houston'
else metros.name
end metros_name
,DATE(case
when metros.id in (24) then convert_tz(tasks.completed_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.completed_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.completed_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.completed_at, 'UTC', 'US/Eastern')
end) Active_Date_Local
,extract(hour from
case when metros.id in (24) then convert_tz(tasks.completed_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.completed_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.completed_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.completed_at, 'UTC', 'US/Eastern')
end) Active_HoD
From tasks
JOIN shipment_companyeries
ON tasks.taskable_id=shipment_companyeries.id
JOIN orders
ON shipment_companyeries.order_id = orders.id
JOIN metros
ON orders.metro_id=metros.id
Where tasks.status NOT IN ('cancelled', 'canceled')
And shipment_companyeries.type <> 'TestDeliveryCompanyery'
and DATE(tasks.actual_start_at) >= <% parameter start_date_placeholder %>
Group By 1,2,3,4
) a
Group By 1,2,3
# models/schema.yml
version: 2
models:
- name: driver_aggregate_worked_hod
columns:
- name: metros_name
tests:
- unique
- not_nullAssumptions
- 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.
driver_aggregate_worked_hod.sql · 63 lines
source-preserved
validation: not checked
Select
metros_name
,Active_Date_Local
,Active_HoD
,count(distinct user_id) as Working_Driver_Count
from
(Select user_id
,case when metros.name IN ('Houston East','Houston Northwest','Houston Southwest','Houston') THEN 'Houston'
else metros.name
end metros_name
,DATE(case
when metros.id in (24) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.actual_start_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.actual_start_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Eastern')
end) Active_Date_Local
,extract(hour from
case when metros.id in (24) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.actual_start_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.actual_start_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Eastern')
end) Active_HoD
From tasks
JOIN shipment_companyeries
ON tasks.taskable_id=shipment_companyeries.id
JOIN orders
ON shipment_companyeries.order_id = orders.id
JOIN metros
ON orders.metro_id=metros.id
Where tasks.status NOT IN ('cancelled', 'canceled')
And shipment_companyeries.type <> 'TestDeliveryCompanyery'
and DATE(tasks.actual_start_at) >= <% parameter start_date_placeholder %>
Group By 1,2,3,4
UNION
Select user_id
,case when metros.name IN ('Houston East','Houston Northwest','Houston Southwest','Houston') THEN 'Houston'
else metros.name
end metros_name
,DATE(case
when metros.id in (24) then convert_tz(tasks.completed_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.completed_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.completed_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.completed_at, 'UTC', 'US/Eastern')
end) Active_Date_Local
,extract(hour from
case when metros.id in (24) then convert_tz(tasks.completed_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.completed_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.completed_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.completed_at, 'UTC', 'US/Eastern')
end) Active_HoD
From tasks
JOIN shipment_companyeries
ON tasks.taskable_id=shipment_companyeries.id
JOIN orders
ON shipment_companyeries.order_id = orders.id
JOIN metros
ON orders.metro_id=metros.id
Where tasks.status NOT IN ('cancelled', 'canceled')
And shipment_companyeries.type <> 'TestDeliveryCompanyery'
and DATE(tasks.actual_start_at) >= <% parameter start_date_placeholder %>
Group By 1,2,3,4
) a
Group By 1,2,3driver_aggregate_worked_hod.view.lkml · 89 lines
inferred
view: driver_aggregate_worked_hod {
derived_table: {
sql: Select
metros_name
,Active_Date_Local
,Active_HoD
,count(distinct user_id) as Working_Driver_Count
from
(Select user_id
,case when metros.name IN ('Houston East','Houston Northwest','Houston Southwest','Houston') THEN 'Houston'
else metros.name
end metros_name
,DATE(case
when metros.id in (24) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.actual_start_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.actual_start_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Eastern')
end) Active_Date_Local
,extract(hour from
case when metros.id in (24) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.actual_start_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.actual_start_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Eastern')
end) Active_HoD
From tasks
JOIN shipment_companyeries
ON tasks.taskable_id=shipment_companyeries.id
JOIN orders
ON shipment_companyeries.order_id = orders.id
JOIN metros
ON orders.metro_id=metros.id
Where tasks.status NOT IN ('cancelled', 'canceled')
And shipment_companyeries.type <> 'TestDeliveryCompanyery'
and DATE(tasks.actual_start_at) >= <% parameter start_date_placeholder %>
Group By 1,2,3,4
UNION
Select user_id
,case when metros.name IN ('Houston East','Houston Northwest','Houston Southwest','Houston') THEN 'Houston'
else metros.name
end metros_name
,DATE(case
when metros.id in (24) then convert_tz(tasks.completed_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.completed_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.completed_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.completed_at, 'UTC', 'US/Eastern')
end) Active_Date_Local
,extract(hour from
case when metros.id in (24) then convert_tz(tasks.completed_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.completed_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.completed_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.completed_at, 'UTC', 'US/Eastern')
end) Active_HoD
From tasks
JOIN shipment_companyeries
ON tasks.taskable_id=shipment_companyeries.id
JOIN orders
ON shipment_companyeries.order_id = orders.id
JOIN metros
ON orders.metro_id=metros.id
Where tasks.status NOT IN ('cancelled', 'canceled')
And shipment_companyeries.type <> 'TestDeliveryCompanyery'
and DATE(tasks.actual_start_at) >= <% parameter start_date_placeholder %>
Group By 1,2,3,4
) a
Group By 1,2,3 ;;
}
dimension: metros_name {
primary_key: yes
sql: ${TABLE}.metros_name ;;
}
dimension_group: Active_Date_Local {
type: time
timeframes: [date]
convert_tz: no
sql: ${TABLE}.Active_Date_Local ;;
}
dimension_group: Active_HoD {
type: number
sql: ${TABLE}.Active_HoD ;;
}
measure: Working_Driver_Count {
type: int
sql: ${TABLE}.Working_Driver_Count ;;
}
}driver_aggregate_worked_hod.view.lookml · 81 lines
source-preserved
- view: driver_aggregate_worked_hod
derived_table:
sql: Select
metros_name
,Active_Date_Local
,Active_HoD
,count(distinct user_id) as Working_Driver_Count
from
(Select user_id
,case when metros.name IN ('Houston East','Houston Northwest','Houston Southwest','Houston') THEN 'Houston'
else metros.name
end metros_name
,DATE(case
when metros.id in (24) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.actual_start_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.actual_start_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Eastern')
end) Active_Date_Local
,extract(hour from
case when metros.id in (24) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.actual_start_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.actual_start_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.actual_start_at, 'UTC', 'US/Eastern')
end) Active_HoD
From tasks
JOIN shipment_companyeries
ON tasks.taskable_id=shipment_companyeries.id
JOIN orders
ON shipment_companyeries.order_id = orders.id
JOIN metros
ON orders.metro_id=metros.id
Where tasks.status NOT IN ('cancelled', 'canceled')
And shipment_companyeries.type <> 'TestDeliveryCompanyery'
and DATE(tasks.actual_start_at) >= <% parameter start_date_placeholder %>
Group By 1,2,3,4
UNION
Select user_id
,case when metros.name IN ('Houston East','Houston Northwest','Houston Southwest','Houston') THEN 'Houston'
else metros.name
end metros_name
,DATE(case
when metros.id in (24) then convert_tz(tasks.completed_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.completed_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.completed_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.completed_at, 'UTC', 'US/Eastern')
end) Active_Date_Local
,extract(hour from
case when metros.id in (24) then convert_tz(tasks.completed_at, 'UTC', 'US/Hawaii')
when metros.id in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(tasks.completed_at, 'UTC', 'US/PACIFIC')
when metros.id in (2,7,15,16,17,23,35) then convert_tz(tasks.completed_at, 'UTC', 'US/CENTRAL')
when metros.id in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(tasks.completed_at, 'UTC', 'US/Eastern')
end) Active_HoD
From tasks
JOIN shipment_companyeries
ON tasks.taskable_id=shipment_companyeries.id
JOIN orders
ON shipment_companyeries.order_id = orders.id
JOIN metros
ON orders.metro_id=metros.id
Where tasks.status NOT IN ('cancelled', 'canceled')
And shipment_companyeries.type <> 'TestDeliveryCompanyery'
and DATE(tasks.actual_start_at) >= <% parameter start_date_placeholder %>
Group By 1,2,3,4
) a
Group By 1,2,3
fields:
- dimension: metros_name
primary_key: true
sql: ${TABLE}.metros_name
- dimension_group: Active_Date_Local
type: time
timeframes:
- date
convert_tz: false
sql: ${TABLE}.Active_Date_Local
- dimension_group: Active_HoD
type: number
sql: ${TABLE}.Active_HoD
- measure: Working_Driver_Count
type: int
sql: ${TABLE}.Working_Driver_CountContents
- driver_aggregate_worked_hod.sql
- driver_aggregate_worked_hod.sql
- driver_aggregate_worked_hod.view.lkml
- driver_aggregate_worked_hod.view.lookml