tracksolid_timescale_grafana_prod/tracksolid_analytics_pipeline.txt

pgcli "postgres://postgres:U1pm3f5SX34DXkHoW6aKFsBHOlMA9binDPNG4aT0FAcg7AubEvYm0e6kU2dZiYrR@stage.rahamafresh.com:5433/tracksolid_db"

postgresql://postgres:U1pm3f5SX34DXkHoW6aKFsBHOlMA9binDPNG4aT0FAcg7AubEvYm0e6kU2dZiYrR@timescale_db-bo3nov2ija7g8wn9b1g2paxs-104717464280:5432/tracksolid_db

---- CURRENT LIVE POSITIONS ------- All devices with a position in the last hour ----
SELECT
    d.device_name,
    d.mc_type,
    ROUND(lp.lat::numeric, 5)  AS lat,
    ROUND(lp.lng::numeric, 5)  AS lng,
    lp.speed,
    lp.acc_status,
    lp.gps_signal,
    lp.gps_num                 AS satellites,
    lp.gps_time AT TIME ZONE 'Africa/Nairobi' AS last_fix_eat,
    ROUND(EXTRACT(EPOCH FROM (now() - lp.gps_time)) / 60.0, 0) AS mins_ago
  FROM tracksolid.live_positions lp
  JOIN tracksolid.devices d ON d.imei = lp.imei
  WHERE lp.gps_time > now() - interval '1 hour'
  ORDER BY lp.gps_time DESC;

----- All 19 live positions — current snapshot ----

SELECT
  d.device_name,
  d.mc_type,
  ROUND(lp.lat::numeric, 5)   AS lat,
  ROUND(lp.lng::numeric, 5)   AS lng,
  lp.speed,
  lp.acc_status,
  lp.gps_time AT TIME ZONE 'Africa/Nairobi' AS last_fix_eat,
  ROUND(EXTRACT(EPOCH FROM (now() - lp.gps_time)) / 3600.0, 1) AS hours_ago,
  lp.current_mileage          AS odometer_km
FROM tracksolid.live_positions lp
JOIN tracksolid.devices d ON d.imei = lp.imei
ORDER BY lp.gps_time DESC;

-------- Devices with no position (silent fleet) -----

SELECT d.imei, d.device_name, d.mc_type, d.sim, d.expiration::date
FROM tracksolid.devices d
LEFT JOIN tracksolid.live_positions lp ON lp.imei = d.imei
WHERE lp.imei IS NULL
ORDER BY d.mc_type, d.device_name;

---- Vehicles currently moving (ACC on OR speed > 0)

SELECT
  d.device_name,
  lp.speed,
  lp.acc_status,
  lp.gps_time AT TIME ZONE 'Africa/Nairobi' AS last_fix_eat
FROM tracksolid.live_positions lp
JOIN tracksolid.devices d ON d.imei = lp.imei
WHERE lp.speed > 0 OR lp.acc_status = '1'
ORDER BY lp.speed DESC;

---- Vehicles in Uganda ----- 

SELECT
  d.device_name,
  ROUND(lp.lat::numeric, 4) AS lat,
  ROUND(lp.lng::numeric, 4) AS lng,
  lp.gps_time AT TIME ZONE 'Africa/Nairobi' AS last_fix_eat
FROM tracksolid.live_positions lp
JOIN tracksolid.devices d ON d.imei = lp.imei
WHERE lp.lat NOT BETWEEN -5.0 AND 5.0
   OR lp.lng NOT BETWEEN 33.9 AND 42.0;

---- Vehicles in Kenya ----- 

SELECT
  d.device_name,
  ROUND(lp.lat::numeric, 4) AS lat,
  ROUND(lp.lng::numeric, 4) AS lng,
  lp.gps_time AT TIME ZONE 'Africa/Nairobi' AS last_fix_eat
FROM tracksolid.live_positions lp
JOIN tracksolid.devices d ON d.imei = lp.imei
WHERE lp.lat  BETWEEN -5.0 AND 5.0
   OR lp.lng  BETWEEN 33.9 AND 42.0;


========== TRIPS AND MOVEMENT ====== 

SELECT
  d.device_name,
  d.vehicle_number,
  d.driver_name,
  COUNT(*)                                        AS trips,
  ROUND(SUM(t.distance_km)::numeric, 2)           AS total_km,
  ROUND(AVG(t.avg_speed_kmh)::numeric, 1)         AS avg_speed_kmh,
  MAX(t.max_speed_kmh)                            AS top_speed_kmh,
  ROUND(SUM(t.driving_time_s) / 3600.0, 2)       AS drive_hours,
  ROUND(SUM(t.idle_time_s) / 3600.0, 2)          AS idle_hours,
  MIN(t.start_time AT TIME ZONE 'Africa/Nairobi') AS day_start,
  MAX(t.end_time   AT TIME ZONE 'Africa/Nairobi') AS day_end
FROM tracksolid.trips t
JOIN tracksolid.devices d ON d.imei = t.imei
WHERE t.start_time >= CURRENT_DATE AT TIME ZONE 'Africa/Nairobi'
GROUP BY d.device_name, d.vehicle_number, d.driver_name
ORDER BY total_km DESC;

------ trips summary LAST 24 HOURS per vehicle  -------

SELECT
    d.device_name,
    t.start_time AT TIME ZONE 'Africa/Nairobi' AS start_eat,
    t.end_time   AT TIME ZONE 'Africa/Nairobi' AS end_eat,
    ROUND(t.distance_km::numeric / 1000.0, 2)   AS distance_km,
    t.avg_speed_kmh,
    t.max_speed_kmh,
    ROUND(t.driving_time_s / 60.0, 0)           AS drive_mins,
    ROUND(t.idle_time_s    / 60.0, 0)           AS idle_mins,
    t.source
  FROM tracksolid.trips t
  JOIN tracksolid.devices d ON d.imei = t.imei
  WHERE t.start_time > now() - interval '24 hours'
  ORDER BY t.start_time DESC;

------- Fleet utilisation rate per vehicle (today)

SELECT
    d.device_name,
    d.driver_name,
    ROUND(SUM(t.driving_time_s) / 3600.0, 2) AS drive_hours,
    ROUND(SUM(t.idle_time_s)    / 3600.0, 2) AS idle_hours,
    LEAST(ROUND(SUM(t.driving_time_s + COALESCE(t.idle_time_s, 0)) / (12.0 * 3600) * 100, 1), 100) AS utilisation_pct
  FROM tracksolid.trips t
  JOIN tracksolid.devices d ON d.imei = t.imei
  WHERE t.start_time >= CURRENT_DATE AT TIME ZONE 'Africa/Nairobi'
  GROUP BY d.device_name, d.driver_name
  ORDER BY utilisation_pct DESC;

 KDK 829A GP           |             |        1.87 |            |            15.5
 FRED KMGW 538W HULETI |             |        1.65 |            |            13.7
 X3-63282              |             |        1.25 |            |            10.4

-----Tracking to this point of the day ----

 SELECT
    d.device_name,
    d.driver_name,
    ROUND(SUM(t.driving_time_s) / 3600.0, 2)                        AS drive_hours,
    ROUND(SUM(t.idle_time_s)    / 3600.0, 2)                        AS idle_hours,
    LEAST(ROUND(
      SUM(t.driving_time_s + COALESCE(t.idle_time_s, 0))
      / (EXTRACT(EPOCH FROM (
          LEAST(now(), CURRENT_DATE::timestamp AT TIME ZONE 'Africa/Nairobi' + interval '19 hours 30 minutes')
          - (CURRENT_DATE::timestamp AT TIME ZONE 'Africa/Nairobi' + interval '7 hours 30 minutes')
      ))) * 100
    , 1), 100)                                                       AS utilisation_pct_so_far
  FROM tracksolid.trips t
  JOIN tracksolid.devices d ON d.imei = t.imei
  WHERE t.start_time >= (CURRENT_DATE::timestamp AT TIME ZONE 'Africa/Nairobi' + interval '7 hours 30 minutes')
  GROUP BY d.device_name, d.driver_name
  ORDER BY utilisation_pct_so_far DESC;


  ------ Vehicles that have not moved today -------

SELECT d.device_name, d.mc_type, d.driver_name
FROM tracksolid.devices d
LEFT JOIN tracksolid.trips t
  ON t.imei = d.imei
  AND t.start_time >= CURRENT_DATE AT TIME ZONE 'Africa/Nairobi'
WHERE t.imei IS NULL
ORDER BY d.device_name;

------ Distance per driver — last 30 days

SELECT
    d.device_name,
    d.driver_name,
    COUNT(DISTINCT DATE(t.start_time AT TIME ZONE 'Africa/Nairobi')) AS active_days,
    COUNT(*)                                                           AS total_trips,
    ROUND(SUM(t.distance_km / 1000.0)::numeric, 0)                   AS total_km,
    ROUND(AVG(t.distance_km / 1000.0)::numeric, 1)                   AS avg_km_per_trip,
    MAX(t.max_speed_kmh)                                              AS top_speed_ever
  FROM tracksolid.trips t
  JOIN tracksolid.devices d ON d.imei = t.imei
  WHERE t.start_time > now() - interval '30 days'
  GROUP BY d.device_name, d.driver_name
  ORDER BY total_km DESC;


  ====== ALL ALARMS =====

  ----Alarms in 24 hours ----

  SELECT
  d.device_name,
  a.alarm_type,
  a.alarm_name,
  a.alarm_time AT TIME ZONE 'Africa/Nairobi' AS alarm_time_eat,
  ROUND(a.lat::numeric, 5)                   AS lat,
  ROUND(a.lng::numeric, 5)                   AS lng,
  a.speed,
  a.severity,
  a.acknowledged_at
FROM tracksolid.alarms a
JOIN tracksolid.devices d ON d.imei = a.imei
WHERE a.alarm_time > now() - interval '24 hours'
ORDER BY a.alarm_time DESC;

----Alarms in 7 Days ----
SELECT
  a.alarm_name,
  a.alarm_type,
  COUNT(*)                                         AS occurrences,
  COUNT(DISTINCT a.imei)                           AS devices_affected,
  MAX(a.alarm_time AT TIME ZONE 'Africa/Nairobi') AS last_seen_eat
FROM tracksolid.alarms a
WHERE a.alarm_time > now() - interval '7 days'
GROUP BY a.alarm_name, a.alarm_type
ORDER BY occurrences DESC;

------ unacknowledged alarms ------ 

SELECT
  d.device_name,
  a.alarm_name,
  a.alarm_time AT TIME ZONE 'Africa/Nairobi' AS alarm_time_eat,
  ROUND(EXTRACT(EPOCH FROM (now() - a.alarm_time)) / 3600.0, 1) AS hours_open
FROM tracksolid.alarms a
JOIN tracksolid.devices d ON d.imei = a.imei
WHERE a.acknowledged_at IS NULL
ORDER BY a.alarm_time DESC;

------ acknowledged alarms ------ 

UPDATE tracksolid.alarms
SET acknowledged_at = now(),
    acknowledged_by = 'operator_name'
WHERE id = <alarm_id>;

===========

------ Position history by source — counts ----

SELECT
  source,
  COUNT(*) AS fixes,
  MIN(gps_time AT TIME ZONE 'Africa/Nairobi') AS earliest,
  MAX(gps_time AT TIME ZONE 'Africa/Nairobi') AS latest
FROM tracksolid.position_history
GROUP BY source;

--------- Route replay for a specific vehicle — last 24 hours

device = name <'FRED KMGW 538W HULETI'> 

SELECT
  ph.gps_time AT TIME ZONE 'Africa/Nairobi' AS gps_time_eat,
  ROUND(ph.lat::numeric, 5)                 AS lat,
  ROUND(ph.lng::numeric, 5)                 AS lng,
  ph.speed,
  ph.direction,
  ph.acc_status,
  ph.source
FROM tracksolid.position_history ph
JOIN tracksolid.devices d ON d.imei = ph.imei
WHERE d.device_name = <'FRED KMGW 538W HULETI'>
  AND ph.gps_time > now() - interval '24 hours'
ORDER BY ph.gps_time ASC;


-------- Fix density per device — last 24 hours --------- 

SELECT
  d.device_name,
  COUNT(*)                                         AS total_fixes,
  COUNT(*) FILTER (WHERE ph.source = 'poll')       AS poll_fixes,
  COUNT(*) FILTER (WHERE ph.source = 'track_list') AS track_list_fixes,
  MIN(ph.gps_time AT TIME ZONE 'Africa/Nairobi')  AS first_fix,
  MAX(ph.gps_time AT TIME ZONE 'Africa/Nairobi')  AS last_fix
FROM tracksolid.position_history ph
JOIN tracksolid.devices d ON d.imei = ph.imei
WHERE ph.gps_time > now() - interval '24 hours'
GROUP BY d.device_name
ORDER BY total_fixes DESC;

### Device & Fleet Registry

-------- Full fleet — all 63 devices ------

SELECT
  device_name,
  mc_type,
  vehicle_number,
  driver_name,
  sim,
  ROUND(current_mileage_km::numeric, 0) AS odometer_km,
  expiration::date                       AS expires,
  enabled_flag
FROM tracksolid.devices
ORDER BY mc_type, device_name;

-------- Fleet by devices model ------

SELECT mc_type, COUNT(*) AS devices
FROM tracksolid.devices
GROUP BY mc_type ORDER BY devices DESC;


------ fleet by odometer readings ----- 

SELECT
  device_name,
  mc_type,
  sim,
  ROUND(current_mileage_km::numeric, 0) AS odometer_km,
  expiration::date                       AS expires
FROM tracksolid.devices
WHERE current_mileage_km IS NOT NULL AND current_mileage_km > 0
ORDER BY current_mileage_km DESC
LIMIT 15;

---- fleet blank driver sim vehicle number blanks ----

SELECT device_name, mc_type, sim
FROM tracksolid.devices
WHERE vehicle_number IS NULL OR vehicle_number = ''
   OR driver_name    IS NULL OR driver_name    = ''
ORDER BY mc_type, device_name;

----- fleet by expiry dates --- 

SELECT
  COUNT(*) FILTER (WHERE expiration IS NULL)                               AS no_expiry_set,
  COUNT(*) FILTER (WHERE expiration < now())                               AS already_expired,
  COUNT(*) FILTER (WHERE expiration BETWEEN now() AND now() + interval '90 days') AS expiring_90days,
  COUNT(*) FILTER (WHERE expiration > now() + interval '90 days')          AS valid_long_term
FROM tracksolid.devices;


-----no of rows in db ----- 

SELECT
  (SELECT COUNT(*) FROM tracksolid.devices)              AS devices,
  (SELECT COUNT(*) FROM tracksolid.live_positions)       AS live_positions,
  (SELECT COUNT(*) FROM tracksolid.position_history)     AS position_history,
  (SELECT COUNT(*) FROM tracksolid.trips)                AS trips,
  (SELECT COUNT(*) FROM tracksolid.alarms)               AS alarms,
  (SELECT COUNT(*) FROM tracksolid.parking_events)       AS parking_events,
  (SELECT COUNT(*) FROM tracksolid.obd_readings)         AS obd_readings,
  (SELECT COUNT(*) FROM tracksolid.device_events)        AS device_events,
  (SELECT COUNT(*) FROM tracksolid.fuel_readings)        AS fuel_readings,
  (SELECT COUNT(*) FROM tracksolid.temperature_readings) AS temperature_readings,
  (SELECT COUNT(*) FROM tracksolid.lbs_readings)         AS lbs_readings,
  (SELECT COUNT(*) FROM tracksolid.ingestion_log)        AS ingestion_log;

===== 

  SELECT 'devices'              AS table_name, COUNT(*) FROM tracksolid.devices
  UNION ALL
  SELECT 'live_positions',               COUNT(*) FROM tracksolid.live_positions
  UNION ALL
  SELECT 'position_history',             COUNT(*) FROM tracksolid.position_history
  UNION ALL
  SELECT 'trips',                        COUNT(*) FROM tracksolid.trips
  UNION ALL
  SELECT 'alarms',                       COUNT(*) FROM tracksolid.alarms
  UNION ALL
  SELECT 'parking_events',               COUNT(*) FROM tracksolid.parking_events
  UNION ALL
  SELECT 'obd_readings',                 COUNT(*) FROM tracksolid.obd_readings
  UNION ALL
  SELECT 'device_events',                COUNT(*) FROM tracksolid.device_events
  UNION ALL
  SELECT 'fuel_readings',                COUNT(*) FROM tracksolid.fuel_readings
  UNION ALL
  SELECT 'temperature_readings',         COUNT(*) FROM tracksolid.temperature_readings
  UNION ALL
  SELECT 'lbs_readings',                 COUNT(*) FROM tracksolid.lbs_readings
  UNION ALL
  SELECT 'ingestion_log',                COUNT(*) FROM tracksolid.ingestion_log
  ORDER BY table_name;