example.md

All of the SQL queries below are wrapping in SQL views stored on BigQuery, they may use other tables or views undernear, but this is what we connect to in Tableau data source

Days

The raw query is:

SELECT 
  date
FROM
  UNNEST(GENERATE_DATE_ARRAY('2017-01-01','2022-01-01')) as date

This is materialised into a static table with just a date field

Mission Times Daily

Joined to days using days.data = mission_date_utc

SELECT
  DATE(m.mission_date_utc)                                                            as mission_date_utc,
  m.region                                                                            as mission_region,
  m.kind                                                                              as kind,
  m.zone_at_assignment                                                                as zone_at_assignment,
  m.child_zone_at_assignment                                                          as child_zone_at_assignment,
  m.oct_zone_at_assignment                                                            as oct_zone_at_assignment,
  ROUND(SUM(TIMESTAMP_DIFF(finished_at_utc, accepted_by_courier_at_utc, SECOND)/60/60),2)  as calculated_hours_on_missions,
  ROUND(SUM(total_time_spent_on_mission_in_sec/60/60),2)                                   as hours_on_missions,
  ROUND(SUM(total_time_spent_on_waypoints_in_sec/60/60),2)                                 as hours_on_waypoints,
  ROUND(SUM(total_time_on_the_road_in_sec/60/60),2)                                        as hours_on_road
  
FROM
  bi_reporting.missions m
  JOIN bi_reporting.mission_timings t on t.mission_id = m.id

WHERE 
  state = 'complete'

GROUP BY 1,2,3,4,5,6

Order Revenue

A more complex view, joined on days.data = DATE(order_revenue.completed_at)

WITH forecast_amount AS (
  SELECT
    order_id,
    ROUND(SUM(amount*quantity)) as price
    
  FROM
    invoicer.forecast_invoice_lines_current
    
  WHERE
    type != 'cod'
  
  GROUP BY 1
), invoiced_amount AS (
  SELECT
    order_id,
    ROUND(SUM(amount*quantity),2) as price
    
  FROM
    invoicer.invoice_lines_current
    
  WHERE
    type != 'cod'
  
  GROUP BY 1
), defaults AS (
  SELECT
    account_name,
    ROUND(SUM(cost),2) as price
    
  FROM
    sheet_imports.account_default_order_price
   
  GROUP BY 1
), full_data AS (


  SELECT
    client_orders.submitted_at_utc                                        as submitted_at,
    timings.finished_at_utc                                               as completed_at,
    client_orders.client_id                                               as client_id,
    client_orders.id                                                      as order_id,
    client_orders.service_kind                                            as service_kind,
    client_orders.kind                                                    as order_kind,
    pickup.region                                                         as pickup_region,
    pickup.town                                                           as pickup_town,
    CASE
    WHEN invoiced_amount.price > 0 THEN
       STRUCT('invoiced' as revenue_datasource,invoiced_amount.price as revenue)
    WHEN forecast_amount.price > 0 THEN
       STRUCT('forecast' as revenue_datasource, forecast_amount.price as revenue)
    WHEN defaults.price > 0 THEN
       STRUCT('sheet' as revenue_datasource, defaults.price as revenue)
    ELSE
      STRUCT('missing' as revenue_datasource,0 as revenue)
    END as revenue_pair

  FROM
    bi_reporting.client_orders
    JOIN 
      bi_reporting.waypoints pickup on pickup.id = client_orders.pickup_waypoint_id
    JOIN 
      bi_reporting.clients on clients.id = client_orders.client_id
    LEFT JOIN 
      bi_reporting.client_order_timings timings on timings.client_order_id = client_orders.id
    LEFT JOIN 
      forecast_amount on forecast_amount.order_id = client_orders.id
    LEFT JOIN 
      invoiced_amount on invoiced_amount.order_id = client_orders.id
    LEFT JOIN defaults on defaults.account_name = clients.account_name

  WHERE 
    client_orders.state = 'delivery_complete'
)

SELECT 
  * EXCEPT(revenue_pair),
  revenue_pair.revenue_datasource as revenue_datasource,
  revenue_pair.revenue            as revenue 
  

FROM 
  full_data

Client Order Timings

Join to a flat table order_revenue.order_id = client_order_timings.client_order_id

Clients

Join to a flat table order_revenue.client_id = clients.id

Order Revenue Submitted

This is the same as the other order revenue join, except we use the submitted_at field instead of completed_at

Clients Submitted

Same as clients for the submitted view over the completed view

Shift Costs

Joined on days.date = shift_costs.date

WITH extra_costs AS (
  SELECT
    SUM(cost) as daily_extra_costs
 
  FROM
    sheet_imports.per_day_extra_costs
), driver_extra_costs AS (
  SELECT
    SUM(cost) as daily_extra_driver_costs
 
  FROM
    sheet_imports.per_day_driver_extra_costs
), full_data AS (

SELECT
  id                      as id,
  date                    as date,
  region                  as region,
  courier_id              as courier_id,
  normal_minutes          as normal_minutes,
  normal_payable          as normal_payable,
  agency_overtime_minutes as agency_overtime_minutes,
  agency_overtime_payable as agency_payable,
  quiqup_overtime_minutes as quiqup_overtime_minutes,
  quiqup_overtime_payable as quiqup_overtime_payable,
  extras_payable          as extras_payable,
  daily_extra_driver_costs as daily_extra_driver_costs,
  COUNT(courier_id) OVER (PARTITION BY date ORDER BY date) as driver_count_per_day,

   
FROM
  timesheet.timesheet_days_current ts_days
  JOIN driver_extra_costs on true = true
  
)

SELECT
  full_data.*,
  IF(full_data.driver_count_per_day > 0,ROUND(daily_extra_costs / driver_count_per_day,2),0) as daily_extra_costs
  
FROM
  full_data
  JOIN extra_costs on true = true