Penyewaan

Studi Kasus Penyewaan dan Buku Resep SQL

Aktifkan IoT Query sebelum memanfaatkan data untuk membangun analitik komprehensif. Jika Anda belum memilikinya, hubungi kami untuk detail aktivasi - [e-mail]

Perusahaan leasing (terutama bank dan penyedia fleet-leasing) mempertahankan kepemilikan atas kendaraan atau peralatan sementara klien hanya menyewa penggunaannya, sehingga mereka menanggung risiko terkait aset sepanjang kontrak. 

Untuk melindungi nilai residual, menegakkan batasan kontrak (jarak tempuh, geografi, perawatan), dan menyederhanakan kewajiban full-service, mereka mengandalkan Navixy. Data GPS real-time, diagnostik berbasis sensor, dan analitik perilaku memungkinkan mereka memverifikasi kondisi penggunaan, mengotomatiskan penjadwalan servis, mendeteksi masalah mekanis lebih awal, menghitung penalti atau biaya kelebihan kilometer, dan, bila diperlukan, menonaktifkan atau mengambil kembali aset—yang semuanya mengamankan investasi mereka, mengurangi biaya operasional, dan meningkatkan transparansi pelanggan sepanjang seluruh siklus hidup leasing.

Navixy IoT Query akan membantu mengorganisasi berbagai jenis analitik di setiap tahap kontrak leasing. Kontrak leasing melalui beberapa fase yang dapat diprediksi: Onboarding & Asset Setup → Fase Operasional → Pengawasan Risiko & Kepatuhan

Resep SQL berikut dalam buku Anda secara kolektif memantau setiap tonggak penting sepanjang siklus hidup tersebut:

Tahap Siklus Hidup	Tujuan & Tonggak	Kasus Penggunaan / Resep yang Dicakup
Onboarding & Asset Setup	• Daftarkan kendaraan, aktifkan asuransi dan kredensial pengemudi. • Impor aset ke portal klien dengan visibilitas yang benar.	Peringatan Kedaluwarsa Registrasi/Asuransi – tanggal dasar dicatat. Kedaluwarsa SIM Pengemudi – memvalidasi pengemudi sebelum pelepasan.
Perencanaan Perawatan Preventif	• Tetapkan jadwal servis berulang, berbasis jarak tempuh, dan berbasis waktu. • Pastikan penggantian ban musiman.	Inspeksi Rutin berdasarkan Interval – tugas berbasis kalender. Servis berdasarkan Ambang Jarak Tempuh – aturan servis minor/major berbasis km. Pemantauan Jam Mesin – servis berbasis jam untuk mesin.
Batas Penggunaan yang Terikat Kontrak	• Tegakkan tunjangan jarak tempuh dan batas finansial. • Deteksi penggunaan berlebih lebih awal untuk menghindari kejutan di akhir masa kontrak.	Batas Jarak Tempuh & Penalti – pengawasan kilometer tahunan / total kontrak.
Perilaku Pengemudi & Aset Secara Real-time	• Lindungi nilai aset; latih pengemudi. • Identifikasi penyalahgunaan yang membatalkan cakupan “full-service”.	Pengereman Keras. Akselerasi Keras. Belokan Mendadak/Cornering.
Pengawasan Risiko & Kepatuhan	• Jaga aset tetap berada di dalam batas geografis dan kontraktual. • Pertahankan hak untuk menonaktifkan atau mengambil kembali.	Pelanggaran Geofence (Batas Negara) – peringatan instan saat wilayah dilanggar. Deteksi Ignition & Idle – pelacakan pemborosan bahan bakar / penyalahgunaan.

Templat dashboard

Sementara resep SQL di bawah ini memberikan kontrol penuh atas analitik leasing, Anda dapat memulai lebih cepat dengan dashboard siap pakai yang memvisualisasikan metrik penting sepanjang siklus hidup leasing. Templat ini menghilangkan kebutuhan membangun query dan visualisasi dari awal. Impor, sesuaikan parameter, dan mulai memantau kepatuhan, risiko, serta perlindungan aset secara langsung.

Templat ini menangani alur kerja leasing utama: pelacakan kedaluwarsa registrasi dan asuransi, pemantauan SIM pengemudi, deteksi pengereman dan akselerasi keras dengan klasifikasi tingkat keparahan, analisis waktu idle, dan pemantauan aktivitas perangkat.

Impor konfigurasi ke Dashboard Studio, sesuaikan ambang batas untuk kontrak Anda (batas jarak tempuh, tingkat keparahan perilaku, parameter deteksi idle), dan terapkan ruang kerja pemantauan lengkap. Ini sangat cocok ketika tim membutuhkan dashboard operasional untuk kepatuhan harian dan pengawasan risiko tanpa menulis SQL.

Prasyarat:

• IoT Query diaktifkan di lingkungan Anda

• Dashboard Studio terpasang dan dapat diakses

• Setidaknya 72 jam data pelacakan

• Tabel skema standar terisi: tracking_data_core, states, objects, vehicles, employees

Konfigurasi setelah impor:

Setelah mengimpor templat, sesuaikan dengan kontrak leasing dan ambang operasional spesifik Anda:

1. Tinjau rentang waktu default 72 jam dan sesuaikan jika ketersediaan data Anda berbeda.

2. Tetapkan ambang keparahan untuk peristiwa mengemudi dalam parameter query (default: 60+ km/jam/detik untuk peringatan, 80+ km/jam/detik untuk peringatan kritis).

3. Konfigurasikan parameter deteksi idle (default: kecepatan di bawah 5 km/jam, durasi minimum 5 menit).

4. Perbarui label zona geofence pada query penyeberangan batas jika memantau pembatasan wilayah.

5. Gunakan pemilih waktu global untuk menganalisis periode historis atau fokus pada aktivitas terbaru.

JSON templat:

25KB

Leasing Dashboard-schema.json

Open

{
  "id": null,
  "uid": "hello-world",
  "tags": [
    "example",
    "getting-started"
  ],
  "time": {
    "to": "now",
    "from": "now-72h"
  },
  "links": [],
  "style": "dark",
  "title": "Leasing Dashboard",
  "panels": [
    {
      "id": 10,
      "type": "text",
      "title": "Deteksi Ignition & Idle",
      "gridPos": {
        "h": 4,
        "w": 24,
        "x": 0,
        "y": 158
      },
      "options": {
        "mode": "markdown",
        "content": " "
      },
      "x-navixy": {
        "sql": {
          "statement": ""
        },
        "dataset": {
          "shape": "table",
          "columns": {}
        }
      }
    },
    {
      "id": 11,
      "type": "piechart",
      "title": "Pengemudi dengan tanggal kedaluwarsa terdekat",
      "gridPos": {
        "h": 12,
        "w": 12,
        "x": 12,
        "y": 0
      },
      "options": {
        "pieType": "donut"
      },
      "x-navixy": {
        "sql": {
          "params": {},
          "statement": "SELECT \r\n    CASE \r\n        WHEN (DATE(e.driver_license_valid_till) - CURRENT_DATE)::INTEGER < 0 THEN 'Expired'\r\n        ELSE 'Others'\r\n    END AS category,\r\n    COUNT(*) AS value\r\nFROM raw_business_data.employees e\r\nWHERE e.driver_license_valid_till IS NOT NULL\r\nGROUP BY category\r\nORDER BY category"
        },
        "verify": {
          "max_rows": 1000
        },
        "dataset": {
          "shape": "pie",
          "columns": {
            "value": {
              "type": "integer"
            },
            "category": {
              "type": "string"
            }
          }
        }
      }
    },
    {
      "id": 12,
      "type": "piechart",
      "title": "Kendaraan dengan tanggal kedaluwarsa terdekat",
      "gridPos": {
        "h": 12,
        "w": 12,
        "x": 0,
        "y": 0
      },
      "options": {
        "pieType": "donut"
      },
      "x-navixy": {
        "sql": {
          "params": {},
          "statement": "WITH expiry_data AS (\r\n    SELECT \r\n        CASE \r\n            WHEN v.free_insurance_valid_till_date IS NOT NULL\r\n                AND (v.liability_insurance_valid_till IS NULL \r\n                      OR v.free_insurance_valid_till_date <= v.liability_insurance_valid_till)\r\n                THEN v.free_insurance_valid_till_date\r\n            WHEN v.liability_insurance_valid_till IS NOT NULL\r\n                THEN v.liability_insurance_valid_till\r\n            ELSE NULL\r\n        END AS nearest_expiry_date\r\n    FROM raw_business_data.vehicles v\r\n),\r\ncategorized_data AS (\r\n    SELECT \r\n        CASE \r\n            WHEN (DATE(nearest_expiry_date) - CURRENT_DATE)::INTEGER < 0 THEN 'Expired'\r\n            WHEN (DATE(nearest_expiry_date) - CURRENT_DATE)::INTEGER >= 0 \r\n                  AND (DATE(nearest_expiry_date) - CURRENT_DATE)::INTEGER < 30 THEN 'Expires within 30 days'\r\n            ELSE 'Others'\r\n        END AS category\r\n    FROM expiry_data\r\n    WHERE nearest_expiry_date IS NOT NULL\r\n)\r\nSELECT \r\n    category,\r\n    COUNT(*) AS value\r\nFROM categorized_data\r\nGROUP BY category\r\nORDER BY \r\n    CASE category\r\n        WHEN 'Expired' THEN 1\r\n        WHEN 'Expires within 30 days' THEN 2\r\n        WHEN 'Others' THEN 3\r\n    END"
        },
        "verify": {
          "max_rows": 1000
        },
        "dataset": {
          "shape": "pie",
          "columns": {
            "value": {
              "type": "integer"
            },
            "category": {
              "type": "string"
            }
          }
        }
      }
    },
    {
      "id": 13,
      "type": "barchart",
      "title": "Peristiwa Pengereman Keras",
      "gridPos": {
        "h": 11,
        "w": 24,
        "x": 0,
        "y": 12
      },
      "options": {
        "textMode": "auto"
      },
      "x-navixy": {
        "sql": {
          "params": {},
          "statement": "WITH spd AS (\r\n          SELECT\r\n            device_id,\r\n            device_time,\r\n            speed/100.0 AS kmh,\r\n            LAG(speed/100.0) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_kmh,\r\n            EXTRACT(EPOCH FROM (device_time - LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time))) AS dt_sec\r\n          FROM\r\n            raw_telematics_data.tracking_data_core\r\n          WHERE 1=1\r\n        ),\r\n        decels AS (\r\n          SELECT\r\n            device_id,\r\n            device_time,\r\n            (prev_kmh - kmh) / NULLIF(dt_sec, 0) AS decel_kmh_per_sec\r\n          FROM\r\n            spd\r\n          WHERE\r\n            prev_kmh IS NOT NULL\r\n        ),\r\n        events_with_severity AS (\r\n          SELECT \r\n            DATE(d.device_time) AS category,\r\n            CASE \r\n              WHEN d.decel_kmh_per_sec >= 80 THEN 'Critical'\r\n              WHEN d.decel_kmh_per_sec >= 60 THEN 'Warning'\r\n              ELSE 'Normal'\r\n            END AS series\r\n          FROM decels d\r\n          WHERE d.decel_kmh_per_sec >= 60\r\n        )\r\n        SELECT \r\n          category,\r\n          COUNT(*) AS value,\r\n          series\r\n        FROM events_with_severity\r\n        GROUP BY category, series\r\n        ORDER BY series"
        },
        "verify": {
          "max_rows": 1000
        },
        "dataset": {
          "shape": "category_value",
          "columns": {
            "value": {
              "type": "integer"
            },
            "series": {
              "type": "string"
            },
            "category": {
              "type": "date"
            }
          }
        },
        "visualization": {
          "sortOrder": "none",
          "colorPalette": "vibrant"
        }
      }
    },
    {
      "id": 14,
      "type": "barchart",
      "title": "Peristiwa Akselerasi Keras",
      "gridPos": {
        "h": 17,
        "w": 24,
        "x": 0,
        "y": 41
      },
      "options": {
        "orientation": "vertical"
      },
      "x-navixy": {
        "sql": {
          "params": {},
          "statement": "WITH spd AS (\r\n          SELECT\r\n            device_id,\r\n            device_time,\r\n            speed/100.0 AS kmh,\r\n            LAG(speed/100.0) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_kmh,\r\n            EXTRACT(EPOCH FROM (device_time - LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time))) AS dt_sec\r\n          FROM\r\n            raw_telematics_data.tracking_data_core\r\n        ),\r\n        accels AS (\r\n          SELECT\r\n            device_id,\r\n            device_time,\r\n            (kmh - prev_kmh) / NULLIF(dt_sec, 0) AS accel_kmh_per_sec\r\n          FROM\r\n            spd\r\n          WHERE\r\n            prev_kmh IS NOT NULL\r\n        ),\r\n        events_with_severity AS (\r\n          SELECT \r\n            DATE(a.device_time) AS category,\r\n            CASE \r\n              WHEN a.accel_kmh_per_sec >= 80 THEN 'Critical'\r\n              WHEN a.accel_kmh_per_sec >= 60 THEN 'Warning'\r\n              ELSE 'Normal'\r\n            END AS series\r\n          FROM accels a\r\n          WHERE a.accel_kmh_per_sec >= 60\r\n        )\r\n        SELECT \r\n          category,\r\n          COUNT(*) AS value,\r\n          series\r\n        FROM events_with_severity\r\n        GROUP BY category, series\r\n        ORDER BY series"
        },
        "verify": {
          "max_rows": 1000
        },
        "dataset": {
          "shape": "category_value",
          "columns": {
            "value": {
              "type": "integer"
            },
            "series": {
              "type": "string"
            },
            "category": {
              "type": "date"
            }
          }
        },
        "visualization": {
          "colorPalette": "vibrant"
        }
      }
    },
    {
      "id": 15,
      "type": "barchart",
      "title": "Belokan Mendadak / Cornering",
      "gridPos": {
        "h": 13,
        "w": 24,
        "x": 0,
        "y": 23
      },
      "options": {
        "orientation": "vertical"
      },
      "x-navixy": {
        "sql": {
          "params": {},
          "statement": "WITH pts AS (\r\n          SELECT\r\n            device_id,\r\n            device_time,\r\n            latitude/1e7::numeric AS lat,\r\n            longitude/1e7::numeric AS lon,\r\n            LAG(latitude/1e7::numeric) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_lat,\r\n            LAG(longitude/1e7::numeric) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_lon,\r\n            speed/100.0 AS kmh,\r\n            LAG(speed/100.0) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_kmh\r\n          FROM\r\n            raw_telematics_data.tracking_data_core\r\n        ),\r\n        bearing AS (\r\n          SELECT *,\r\n                 atan2(\r\n                   sin(radians(lon-prev_lon))*cos(radians(lat)),\r\n                   cos(radians(prev_lat))*sin(radians(lat)) -\r\n                   sin(radians(prev_lat))*cos(radians(lat))*cos(radians(lon-prev_lon))\r\n                 ) * 180/pi() AS heading_change\r\n          FROM pts\r\n          WHERE prev_lat IS NOT NULL AND prev_lon IS NOT NULL\r\n        ),\r\n        events_with_severity AS (\r\n          SELECT \r\n            DATE(b.device_time) AS category,\r\n            CASE \r\n              WHEN ABS(b.heading_change) >= 50 AND b.kmh >= 30 THEN 'Critical'\r\n              WHEN ABS(b.heading_change) >= 30 AND b.kmh >= 30 THEN 'Warning'\r\n              ELSE 'Normal'\r\n            END AS series\r\n          FROM bearing b\r\n        )\r\n        SELECT \r\n          category,\r\n          COUNT(*) AS value,\r\n          series\r\n        FROM events_with_severity\r\n        GROUP BY category, series\r\n        ORDER BY category, series"
        },
        "verify": {
          "max_rows": 1000
        },
        "dataset": {
          "shape": "category_value",
          "columns": {
            "value": {
              "type": "integer"
            },
            "series": {
              "type": "string"
            },
            "category": {
              "type": "date"
            }
          }
        },
        "visualization": {
          "stacking": "percent",
          "colorPalette": "classic"
        }
      }
    },
    {
      "id": 16,
      "type": "stat",
      "title": "Total Peristiwa Idle",
      "gridPos": {
        "h": 5,
        "w": 8,
        "x": 0,
        "y": 36
      },
      "options": {
        "textMode": "auto"
      },
      "x-navixy": {
        "sql": {
          "params": {},
          "statement": "WITH ign AS (\r\n          SELECT device_id,\r\n                 device_time,\r\n                 value::int AS ign_on\r\n          FROM raw_telematics_data.states\r\n          WHERE state_name = 'ignition'\r\n        ),\r\n        spd AS (\r\n          SELECT device_id, device_time, speed/100.0 AS kmh\r\n          FROM raw_telematics_data.tracking_data_core\r\n        ),\r\n        merged AS (\r\n          SELECT i.device_id,\r\n                 i.device_time,\r\n                 i.ign_on,\r\n                 s.kmh,\r\n                 LEAD(i.device_time) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS next_time\r\n          FROM ign i\r\n          LEFT JOIN spd s ON s.device_id = i.device_id AND s.device_time = i.device_time\r\n        )\r\n        SELECT COUNT(*) AS value\r\n        FROM merged m\r\n        WHERE m.ign_on = 1 \r\n          AND (m.kmh IS NULL OR m.kmh < 5) \r\n          AND m.next_time IS NOT NULL\r\n          AND EXTRACT(EPOCH FROM (m.next_time - m.device_time))/60 >= 5"
        },
        "verify": {
          "max_rows": 1000
        },
        "dataset": {
          "shape": "kpi",
          "columns": {}
        }
      }
    },
    {
      "id": 9,
      "type": "timeseries",
      "title": "Pesan Seiring Waktu",
      "gridPos": {
        "h": 13,
        "w": 24,
        "x": 0,
        "y": 83
      },
      "options": {
        "legend": {
          "calcs": [],
          "placement": "bottom",
          "showLegend": true,
          "displayMode": "list"
        },
        "tooltip": {
          "mode": "single",
          "sort": "none"
        }
      },
      "targets": [],
      "x-navixy": {
        "sql": {
          "params": {},
          "statement": "WITH bounds AS (\nSELECT date_trunc('hour', NOW() - INTERVAL '24 hours') AS start_ts, date_trunc('hour', NOW()) AS end_ts ), hours AS (\nSELECT generate_series( (\nSELECT start_ts\nFROM bounds), (\nSELECT end_ts\nFROM bounds), INTERVAL '1 hour' ) AS bucket ), counts AS (\nSELECT date_trunc('hour', t.device_time) AS bucket, COUNT(*) AS messages, COUNT(DISTINCT t.device_id) AS unique_devices\nFROM raw_telematics_data.tracking_data_core t --\n    JOIN raw_business_data.objects o\n  ON o.device_id = t.device_id --\n  AND o.client_id = 398286 -- uncomment & set if you want a specific client\nWHERE t.device_time >= (\nSELECT start_ts\nFROM bounds)\n  AND t.device_time < (\nSELECT end_ts\nFROM bounds) + INTERVAL '1 hour'\nGROUP BY 1 )\nSELECT h.bucket AS time, COALESCE(c.messages, 0) AS messages, COALESCE(c.unique_devices, 0) AS unique_devices\nFROM hours h LEFT\n    JOIN counts c USING (bucket)\nORDER BY time;"
        },
        "verify": {
          "max_rows": 1000
        },
        "dataset": {
          "shape": "time_value",
          "columns": {}
        },
        "visualization": {
          "lineStyle": "solid",
          "colorPalette": "modern",
          "interpolation": "smooth",
          "legendPosition": "top"
        }
      },
      "datasource": null,
      "fieldConfig": {
        "defaults": {
          "unit": "short",
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "stacking": {
              "mode": "none",
              "group": "A"
            },
            "drawStyle": "line",
            "lineWidth": 1,
            "spanNulls": false,
            "showPoints": "auto",
            "fillOpacity": 10,
            "gradientMode": "none",
            "axisPlacement": "auto",
            "lineInterpolation": "linear"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              }
            ]
          }
        },
        "overrides": []
      }
    },
    {
      "id": 1,
      "type": "kpi",
      "title": "Jumlah Sampel",
      "gridPos": {
        "h": 5,
        "w": 6,
        "x": 18,
        "y": 128
      },
      "options": {
        "textMode": "auto",
        "colorMode": "value",
        "graphMode": "none",
        "justifyMode": "auto",
        "orientation": "auto"
      },
      "targets": [],
      "x-navixy": {
        "sql": {
          "params": {
            "tenant_id": {
              "type": "uuid"
            }
          },
          "statement": "SELECT *\nFROM raw_telematics_data.tracking_data_core LIMIT 10;"
        },
        "verify": {
          "max_rows": 1
        },
        "dataset": {
          "shape": "kpi",
          "columns": {
            "value": {
              "type": "number"
            }
          }
        }
      },
      "datasource": null,
      "description": "",
      "fieldConfig": {
        "defaults": {
          "unit": "short",
          "color": {
            "mode": "thresholds"
          },
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              }
            ]
          }
        },
        "overrides": []
      }
    },
    {
      "id": 2,
      "type": "barchart",
      "title": "Data Sampel berdasarkan Kategori",
      "gridPos": {
        "h": 15,
        "w": 18,
        "x": 0,
        "y": 130
      },
      "options": {
        "valueMode": "color",
        "displayMode": "gradient",
        "orientation": "horizontal",
        "showUnfilled": true
      },
      "targets": [],
      "x-navixy": {
        "sql": {
          "params": {},
          "statement": "SELECT o.object_label, COUNT(*) AS msgs_24h\nFROM raw_telematics_data.tracking_data_core AS t\n    JOIN raw_business_data.objects AS o\n  ON o.device_id = t.device_id\nWHERE t.device_time >= NOW() - INTERVAL '24 hours'\nGROUP BY o.client_id, o.object_label, t.device_id\nORDER BY msgs_24h DESC LIMIT 20;"
        },
        "verify": {
          "max_rows": 10
        },
        "dataset": {
          "shape": "category_value",
          "columns": {}
        },
        "visualization": {
          "orientation": "vertical"
        }
      },
      "datasource": null,
      "fieldConfig": {
        "defaults": {
          "unit": "short",
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "hideFrom": {
              "viz": false,
              "legend": false,
              "tooltip": false
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              }
            ]
          }
        },
        "overrides": []
      }
    },
    {
      "id": 3,
      "type": "table",
      "title": "Pesan per hari",
      "gridPos": {
        "h": 9,
        "w": 12,
        "x": 4,
        "y": 148
      },
      "options": {
        "sortBy": [],
        "showHeader": true
      },
      "targets": [],
      "x-navixy": {
        "sql": {
          "params": {
            "__to": null,
            "__from": null
          },
          "statement": "SELECT TO_CHAR(date_trunc('day', t.device_time), 'Mon DD, YYYY') AS \"Date\", \n       COUNT(*) AS \"Messages\"\nFROM raw_telematics_data.tracking_data_core AS t\nWHERE t.device_time >= ${__from}\n  AND t.device_time < ${__to}\nGROUP BY 1\nORDER BY 1;"
        },
        "verify": {
          "max_rows": 10
        },
        "dataset": {
          "shape": "table",
          "columns": {}
        },
        "visualization": {
          "pageSize": 5
        }
      },
      "datasource": null,
      "fieldConfig": {
        "defaults": {
          "custom": {
            "align": "auto",
            "displayMode": "auto"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              }
            ]
          }
        },
        "overrides": []
      }
    },
    {
      "id": 6,
      "type": "kpi",
      "title": "Total Waktu Idle",
      "gridPos": {
        "h": 5,
        "w": 8,
        "x": 8,
        "y": 36
      },
      "options": {
        "textMode": "auto"
      },
      "x-navixy": {
        "sql": {
          "params": {},
          "statement": "WITH ign AS (\n    SELECT device_id,\n            device_time,\n            value::int AS ign_on\n    FROM raw_telematics_data.states\n    WHERE state_name = 'ignition'\n  ),\n  spd AS (\n    SELECT device_id, device_time, speed/100 AS kmh\n    FROM raw_telematics_data.tracking_data_core\n  ),\n  merged AS (\n    SELECT i.device_id,\n            i.device_time,\n            i.ign_on,\n            s.kmh,\n            LEAD(i.device_time) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS next_time\n    FROM ign i\n    LEFT JOIN spd s ON s.device_id = i.device_id AND s.device_time = i.device_time\n  )\n  SELECT round(COALESCE(SUM(EXTRACT(EPOCH FROM (m.next_time - m.device_time))/60), 0), 0) AS value\n  FROM merged m\n  WHERE m.ign_on = 1 \n    AND (m.kmh IS NULL OR m.kmh < 5) \n    AND m.next_time IS NOT NULL\n    AND EXTRACT(EPOCH FROM (m.next_time - m.device_time))/60 >= 5"
        },
        "verify": {
          "max_rows": 1000
        },
        "dataset": {
          "shape": "kpi",
          "columns": {
            "value": {
              "type": "number"
            }
          }
        }
      }
    },
    {
      "id": 7,
      "type": "piechart",
      "title": "Perangkat aktif teratas dalam 24 jam terakhir",
      "gridPos": {
        "h": 9,
        "w": 12,
        "x": 0,
        "y": 121
      },
      "options": {
        "pieType": "donut"
      },
      "x-navixy": {
        "sql": {
          "params": {},
          "statement": "SELECT o.object_label, COUNT(*) AS msgs_24h\nFROM raw_telematics_data.tracking_data_core AS t\n    JOIN raw_business_data.objects AS o\n  ON o.device_id = t.device_id\nWHERE t.device_time >= NOW() - INTERVAL '24 hours'\nGROUP BY o.client_id, o.object_label, t.device_id\nORDER BY msgs_24h DESC LIMIT 20;"
        },
        "verify": {
          "max_rows": 1000
        },
        "dataset": {
          "shape": "pie",
          "columns": {}
        }
      }
    },
    {
      "id": 8,
      "type": "table",
      "title": "Pergerakan Kendaraan Terkini",
      "gridPos": {
        "h": 25,
        "w": 24,
        "x": 0,
        "y": 96
      },
      "options": {
        "showHeader": true
      },
      "x-navixy": {
        "sql": {
          "params": {},
          "statement": "SELECT\n  to_char(t.device_time, 'YYYY-MM-DD HH24:MI:SS TZ') AS \"Timestamp\",\n  t.device_id                                       AS \"Device ID\",\n  t.speed                                           AS \"Speed\",\n  round(t.latitude::numeric  / 10000000, 6)         AS \"Latitude (°)\",\n  round(t.longitude::numeric / 10000000, 6)         AS \"Longitude (°)\"\nFROM raw_telematics_data.tracking_data_core t\nWHERE t.device_time >= NOW() - INTERVAL '3 days'\n  AND t.speed > 0\nORDER BY t.device_time DESC;"
        },
        "verify": {
          "max_rows": 3
        },
        "dataset": {
          "shape": "table",
          "columns": {
            "Speed": {
              "type": "integer"
            },
            "Device ID": {
              "type": "integer"
            },
            "Timestamp": {
              "type": "string"
            },
            "Latitude (°)": {
              "type": "number"
            },
            "Longitude (°)": {
              "type": "number"
            }
          }
        },
        "visualization": {
          "pageSize": 25,
          "sortable": true,
          "showHeader": true,
          "showTotals": false,
          "rowHighlighting": "hover"
        }
      }
    },
    {
      "id": 19,
      "type": "stat",
      "title": "Durasi Idle Rata-rata",
      "gridPos": {
        "h": 5,
        "w": 8,
        "x": 16,
        "y": 36
      },
      "options": {
        "textMode": "auto"
      },
      "x-navixy": {
        "sql": {
          "params": {},
          "statement": "WITH ign AS (\r\n          SELECT device_id,\r\n                 device_time,\r\n                 value::int AS ign_on\r\n          FROM raw_telematics_data.states\r\n          WHERE state_name = 'ignition'\r\n        ),\r\n        spd AS (\r\n          SELECT device_id, device_time, speed/100.0 AS kmh\r\n          FROM raw_telematics_data.tracking_data_core\r\n        ),\r\n        merged AS (\r\n          SELECT i.device_id,\r\n                 i.device_time,\r\n                 i.ign_on,\r\n                 s.kmh,\r\n                 LEAD(i.device_time) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS next_time\r\n          FROM ign i\r\n          LEFT JOIN spd s ON s.device_id = i.device_id AND s.device_time = i.device_time\r\n        )\r\n        SELECT round(COALESCE(AVG(EXTRACT(EPOCH FROM (m.next_time - m.device_time))/60), 0),0) AS value\r\n        FROM merged m\r\n        WHERE m.ign_on = 1 \r\n          AND (m.kmh IS NULL OR m.kmh < 5) \r\n          AND m.next_time IS NOT NULL\r\n          AND EXTRACT(EPOCH FROM (m.next_time - m.device_time))/60 >= 5"
        },
        "verify": {
          "max_rows": 1000
        },
        "dataset": {
          "shape": "kpi",
          "columns": {}
        }
      }
    }
  ],
  "refresh": "30s",
  "version": 1,
  "editable": true,
  "timezone": "browser",
  "x-navixy": {
    "execution": {
      "dialect": "postgresql",
      "endpoint": "/api/v1/sql/run",
      "max_rows": 1000,
      "read_only": true,
      "timeout_ms": 5000,
      "allowed_schemas": [
        "demo_data"
      ]
    },
    "parameters": {
      "bindings": {
        "to": "${__to}",
        "from": "${__from}",
        "tenant_id": "${var_tenant}"
      }
    },
    "schemaVersion": "1.0.0"
  },
  "templating": {
    "list": [
      {
        "name": "var_tenant",
        "type": "constant",
        "label": "Tenant",
        "query": "demo-tenant-id",
        "current": {
          "text": "Demo Tenant",
          "value": "demo-tenant-id"
        },
        "options": [
          {
            "text": "Demo Tenant",
            "value": "demo-tenant-id",
            "selected": true
          }
        ]
      }
    ],
    "enable": true
  },
  "timepicker": {
    "now": true,
    "enable": true,
    "hidden": false,
    "collapse": false,
    "time_options": [
      "5m",
      "15m",
      "1h",
      "6h",
      "12h",
      "24h"
    ],
    "refresh_intervals": [
      "5s",
      "10s",
      "30s",
      "1m",
      "5m",
      "15m",
      "30m",
      "1h"
    ]
  },
  "annotations": {
    "list": [
      {
        "hide": true,
        "name": "Anotasi & Peringatan",
        "type": "dashboard",
        "enable": true,
        "target": {
          "tags": [],
          "type": "dashboard",
          "limit": 100,
          "matchAny": false
        },
        "builtIn": 1,
        "iconColor": "rgba(0, 211, 255, 1)",
        "datasource": {
          "uid": "-- Dashboard --",
          "type": "dashboard"
        }
      }
    ]
  },
  "description": "Contoh dashboard sederhana untuk memulai",
  "graphTooltip": 1,
  "schemaVersion": 38
}

Untuk mempelajari lebih lanjut tentang aplikasi dashboard IoT Querie, lihat Dashboard Studio.

Untuk bantuan penyiapan, hubungi [e-mail].

Peringatan Kedaluwarsa Registrasi / Asuransi

Bank harus melacak kedaluwarsa registrasi dan asuransi yang akan datang karena mereka bertanggung jawab atas inspeksi teknis, registrasi, dan asuransi. Peringatan tepat waktu mencegah denda dan downtime kendaraan.

SELECT 
    v.vehicle_id,
    v.vehicle_label,
    v.registration_number,
    v.free_insurance_valid_till_date,
    v.liability_insurance_valid_till
FROM raw_business_data.vehicles v
WHERE v.free_insurance_valid_till_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days'
    OR v.liability_insurance_valid_till BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days';

Kedaluwarsa SIM Pengemudi

Meskipun tidak selalu wajib, menyediakan peringatan proaktif kedaluwarsa SIM merupakan layanan bernilai tambah. Peringatan dini memungkinkan klien memperbarui SIM sebelum habis masa berlakunya. Harap diperhatikan Anda

SELECT e.employee_id,
       e.first_name || ' ' || e.last_name AS driver_name,
       e.driver_license_number,
       e.driver_license_valid_till
FROM raw_business_data.employees e
WHERE e.driver_license_valid_till BETWEEN CURRENT_DATE AND CURRENT_DATE + (30 * INTERVAL '1 day');

Pelanggaran Geofence (Batas Negara)

Kontrak dapat membatasi pergerakan kendaraan ke wilayah tertentu (misalnya Serbia). Keluar dari zona tersebut harus segera memberi tahu bank agar dapat mengambil tindakan (misalnya, menghubungi klien, menonaktifkan aset).

Kueri SQL ini dirancang untuk memantau dan mengidentifikasi saat sebuah perangkat keluar dari zona geografis yang telah ditentukan berlabel "Tallaght Depot Geofences." Proses dimulai dengan mengumpulkan dan mengurutkan titik geografis yang mendefinisikan batas zona. Untuk memastikan batas membentuk poligon yang valid, titik pertama ditambahkan ke akhir daftar, yang secara efektif menutup bentuk. Kumpulan titik yang tertutup ini kemudian digunakan untuk membuat poligon yang merepresentasikan zona geografis, yang dikonversi menjadi objek geography untuk analisis spasial.

Kueri kemudian mengambil data pelacakan perangkat dalam rentang waktu yang ditentukan, mengonversi nilai lintang dan bujur mentah menjadi titik geografis. Kueri menghitung apakah setiap titik perangkat berada di dalam atau di luar zona yang telah ditentukan menggunakan fungsi ST_Contains, yang memeriksa kontainment spasial. Parameter yang dihitung pos menunjukkan 'inside' jika titik berada di dalam zona dan 'outside' jika tidak. Terakhir, kueri memfilter hasil ini untuk mendeteksi transisi saat perangkat bergerak dari dalam zona ke luar, menggunakan fungsi jendela untuk membandingkan posisi saat ini dengan posisi sebelumnya. Logika ini membantu dalam memantau pergerakan perangkat dan mendeteksi kejadian keluar dari area geografis tertentu. Pastikan Anda menambahkan nilai yang benar untuk parameter: z.zone_label = 'your_zone_label'.

WITH zone AS (
  SELECT z.zone_id,
         ST_MakePolygon(ST_MakeLine(ARRAY_AGG(ST_MakePoint(g.longitude, g.latitude) ORDER BY g.number)))::geography AS geog
  FROM raw_business_data.zones z
  JOIN raw_business_data.geofence_points g ON g.zone_id = z.zone_id
  WHERE z.zone_label = 'your_zone_label'
  GROUP BY z.zone_id
),
pts AS (
  SELECT device_id,
         device_time,
         ST_SetSRID(ST_MakePoint(longitude/1e7::numeric, latitude/1e7::numeric), 4326)::geography AS geog
  FROM raw_telematics_data.tracking_data_core
  WHERE device_time BETWEEN '2025-07-27 00:00:00' AND '2025-07-28 23:59:59'
),
states AS (
  SELECT p.*,
         CASE WHEN ST_Contains(z.geog::geometry, p.geog::geometry) THEN 'inside' ELSE 'outside' END AS pos
  FROM pts p CROSS JOIN zone z
),
filtered_states AS (
  SELECT
    device_id,
    device_time,
    pos,
    LAG(pos) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_pos
  FROM states
)
SELECT device_id, device_time, pos
FROM filtered_states
WHERE prev_pos = 'inside' AND pos = 'outside';

Inspeksi Rutin berdasarkan Interval Waktu

Beberapa tugas perawatan berulang pada jadwal waktu tetap. Sistem harus menandai kendaraan yang inspeksi/cek berikutnya jatuh tempo dalam interval yang ditentukan.

WITH t AS (
    SELECT
        vehicle_id,
        description,
        start_date,
        date_repeat_interval,
        make_interval(days => date_repeat_interval) AS repeat_interval
    FROM raw_business_data.vehicle_service_tasks
    WHERE date_repeat_interval IS NOT NULL
)
SELECT
    vehicle_id,
    description,
    start_date,
    date_repeat_interval,
    start_date
        + repeat_interval
            * floor(
                extract(epoch from (current_date::timestamp - start_date))
                / extract(epoch from repeat_interval)
              ) AS last_due,
    start_date
        + repeat_interval
            * (
                floor(
                    extract(epoch from (current_date::timestamp - start_date))
                    / extract(epoch from repeat_interval)
                ) + 1
              ) AS next_due
FROM t
WHERE (
        start_date
            + repeat_interval
                * (
                    floor(
                        extract(epoch from (current_date::timestamp - start_date))
                        / extract(epoch from repeat_interval)
                    ) + 1
                  )
      ) BETWEEN current_date
          AND (current_date + interval '30 days');

Servis berdasarkan Ambang Jarak Tempuh (Minor/Major)

Servis minor dan major dipicu oleh jarak tempuh sejak kejadian servis terakhir. Saat kilometer yang terakumulasi melebihi ambang batas, servis yang sesuai harus dijadwalkan.

Harap diperhatikan bahwa field vst.description harus memiliki komentar / deskripsi yang relevan untuk menggunakannya sebagai filter dalam kode SQL di bawah ini.

SELECT
  v.vehicle_id,
  v.vehicle_label,
  km.km_since_service,
  vst.mileage_limit
FROM
  raw_business_data.vehicles v
  JOIN LATERAL (
    SELECT MAX(vst.completion_date) AS last_service_date
    FROM raw_business_data.vehicle_service_tasks vst
    WHERE vst.vehicle_id = v.vehicle_id
      AND (vst.description ILIKE '%minor%' OR vst.description ILIKE '%major%')
      AND vst.completion_date IS NOT NULL
  ) ls ON TRUE
  JOIN raw_business_data.objects o ON o.object_id = v.vehicle_id
  JOIN LATERAL (
    SELECT SUM(t.trip_distance_meters) / 1000.0 AS km_since_service
    FROM processed_common_data.trips t
    WHERE t.device_id = o.device_id
      AND t.trip_start_time > ls.last_service_date
  ) km ON TRUE
  JOIN raw_business_data.vehicle_service_tasks vst
    ON vst.vehicle_id = v.vehicle_id
    AND vst.completion_date = ls.last_service_date
    AND (vst.description ILIKE '%minor%' OR vst.description ILIKE '%major%')

Batas Jarak Tempuh & Penalti

Kontrak leasing sering menetapkan batas jarak tempuh (misalnya, 25.000 km/tahun). Jika batas terlampaui, klausul penalti berlaku. Sistem harus membandingkan jarak tempuh aktual selama periode kontrak dengan batas yang disepakati dan menghitung biaya.

WITH driven AS (
  SELECT
    o.object_id,
    DATE_TRUNC('year', t.trip_start_time) AS year,
    SUM(t.trip_distance_meters) / 1000.0 AS km_year
  FROM
    processed_common_data.trips t
    JOIN raw_business_data.objects o ON o.device_id = t.device_id
  WHERE
    t.trip_start_time >= '2023-01-01'::date
    AND t.trip_start_time < '2024-01-01'::date
  GROUP BY
    o.object_id, DATE_TRUNC('year', t.trip_start_time)
),
limits AS (
  SELECT
    object_id,
    10000 AS km_limit,
    0.5 AS penalty_rate
  FROM
    raw_business_data.objects
)
SELECT
  d.object_id,
  d.year,
  d.km_year,
  l.km_limit,
  GREATEST(d.km_year - l.km_limit, 0) AS km_over,
  GREATEST(d.km_year - l.km_limit, 0) * l.penalty_rate AS penalty_amount
FROM
  driven d
  JOIN limits l ON d.object_id = l.object_id;

Pemantauan Jam Mesin

Untuk alat berat dan peralatan pertanian, jam operasional—bukan jarak tempuh—menentukan pemeliharaan dan penagihan. Data jam mesin (misalnya, dari CAN-Bus) harus dipantau dan diringkas.

WITH last_service AS (
  SELECT
    vst.vehicle_id,
    MAX(vst.completion_date) AS last_service_date,
    MAX(vst.completion_engine_hours) AS last_service_engine_hours
  FROM
    raw_business_data.vehicle_service_tasks vst
  GROUP BY
    vst.vehicle_id
),
engine_hours_since_service AS (
  SELECT
    v.vehicle_id,
    SUM(t.trip_duration_seconds) / 3600.0 AS engine_hours_since_service
  FROM
    raw_business_data.vehicles v
    JOIN raw_business_data.objects o ON o.object_id = v.object_id
    JOIN processed_common_data.trips t ON t.device_id = o.device_id
    JOIN last_service ls ON ls.vehicle_id = v.vehicle_id
  WHERE
    t.trip_start_time > ls.last_service_date
  GROUP BY
    v.vehicle_id
)
SELECT
  v.vehicle_id,
  v.vehicle_label,
  ls.last_service_engine_hours,
  ehs.engine_hours_since_service,
  (COALESCE(ehs.engine_hours_since_service,0) + COALESCE(ls.last_service_engine_hours,0)) AS current_engine_hours,
  vst.engine_hours_limit
FROM
  raw_business_data.vehicles v
  JOIN last_service ls ON ls.vehicle_id = v.vehicle_id
  LEFT JOIN engine_hours_since_service ehs ON ehs.vehicle_id = v.vehicle_id
  JOIN raw_business_data.vehicle_service_tasks vst
    ON vst.vehicle_id = v.vehicle_id
    AND vst.completion_date = ls.last_service_date

Peristiwa Pengereman Keras

Perilaku berkendara memengaruhi keausan dan kepatuhan terhadap kontrak. Mendeteksi pengereman keras membantu bank mengaitkan keausan rem/ban dini dengan penyalahgunaan oleh pengemudi dan, jika perlu, memindahkan biaya.

Kueri SQL di bawah ini pertama-tama menghitung kecepatan dalam kilometer per jam dan selisih waktu antara titik data berurutan untuk setiap perangkat. Dengan informasi ini, kueri kemudian menghitung laju deselerasi dalam kilometer per jam per detik. Terakhir, kueri memfilter dan mengembalikan catatan di mana laju deselerasi adalah 20 km/jam per detik atau lebih tinggi, yang menunjukkan peristiwa deselerasi signifikan.

WITH spd AS (
  SELECT
    device_id,
    device_time,
    speed/100.0 AS kmh,
    LAG(speed/100.0) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_kmh,
    EXTRACT(EPOCH FROM (device_time - LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time))) AS dt_sec
  FROM
    raw_telematics_data.tracking_data_core
  WHERE
    device_time BETWEEN '2025-07-24 00:00:00' AND '2025-07-24 23:59:59'
),
decels AS (
  SELECT
    device_id,
    device_time,
    (prev_kmh - kmh) / NULLIF(dt_sec, 0) AS decel_kmh_per_sec
  FROM
    spd
  WHERE
    prev_kmh IS NOT NULL
)
SELECT *
FROM decels
WHERE decel_kmh_per_sec >= 20;

Peristiwa Akselerasi Keras

Akselerasi agresif meningkatkan keausan ban, transmisi, drivetrain, dan dudukan mesin. Mengidentifikasi peristiwa ini mendukung pembinaan dan potensi pemulihan biaya.

Kueri SQL di bawah ini dirancang untuk mengidentifikasi peristiwa akselerasi signifikan dari kumpulan data pelacakan. Kueri ini pertama-tama menghitung kecepatan dalam kilometer per jam dan selisih waktu antara titik data berurutan untuk setiap perangkat. Dengan informasi ini, kueri kemudian menghitung laju akselerasi dalam kilometer per jam per detik. Terakhir, kueri memfilter dan mengembalikan catatan di mana laju akselerasi memenuhi atau melebihi ambang batas yang ditentukan, yang menunjukkan peristiwa akselerasi signifikan.

WITH spd AS (
  SELECT
    device_id,
    device_time,
    speed/100.0 AS kmh,
    LAG(speed/100.0) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_kmh,
    EXTRACT(EPOCH FROM (device_time - LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time))) AS dt_sec
  FROM
    raw_telematics_data.tracking_data_core
  WHERE
    device_time BETWEEN '2025-07-28 00:00:00' AND '2025-07-28 23:59:59'
)
SELECT
  device_id,
  device_time,
  (kmh - prev_kmh) / NULLIF(dt_sec, 0) AS accel_kmh_per_sec
FROM
  spd
WHERE
  prev_kmh IS NOT NULL
  AND (kmh - prev_kmh) / NULLIF(dt_sec, 0) >= 20;

Belokan Mendadak / Cornering

Belokan tajam yang dikombinasikan dengan perubahan kecepatan yang tiba-tiba menunjukkan berkendara yang berisiko. Melacak perilaku seperti ini membantu mendeteksi penggunaan kendaraan yang tidak semestinya.

Kueri SQL ini dirancang untuk mengidentifikasi perubahan arah dan kecepatan yang signifikan dari data pelacakan selama periode waktu tertentu. Kueri ini pertama-tama mengonversi nilai lintang dan bujur mentah menjadi derajat desimal dan menghitung kecepatan dalam kilometer per jam. Menggunakan fungsi LAG, kueri mengambil lokasi dan data kecepatan sebelumnya untuk setiap perangkat, sehingga memungkinkan perhitungan perubahan dari waktu ke waktu. Kueri kemudian menghitung perubahan arah dalam derajat menggunakan fungsi trigonometri untuk menentukan bearing antara titik-titik berurutan. Kueri ini juga menghitung perubahan kecepatan antara titik-titik tersebut. Terakhir, kueri memfilter hasil untuk hanya menyertakan catatan di mana perubahan arah absolut adalah 10 derajat atau lebih dan perubahan kecepatan absolut adalah 5 km/jam atau lebih, sehingga mengidentifikasi manuver atau peristiwa signifikan dalam data pelacakan.

WITH pts AS (
  SELECT
    device_id,
    device_time,
    latitude/1e7::numeric AS lat,
    longitude/1e7::numeric AS lon,
    LAG(latitude/1e7::numeric) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_lat,
    LAG(longitude/1e7::numeric) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_lon,
    speed/100.0 AS kmh,
    LAG(speed/100.0) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_kmh
  FROM
    raw_telematics_data.tracking_data_core
  WHERE
    device_time BETWEEN '2025-07-28 00:00:00' AND '2025-07-28 23:59:59'
),
bearing AS (
  SELECT *,
         atan2(
           sin(radians(lon-prev_lon))*cos(radians(lat)),
           cos(radians(prev_lat))*sin(radians(lat)) -
           sin(radians(prev_lat))*cos(radians(lat))*cos(radians(lon-prev_lon))
         ) * 180/pi() AS heading_change,
         (kmh - prev_kmh) AS delta_speed
  FROM pts
)
SELECT *
FROM bearing
WHERE abs(heading_change) >= 10
  AND abs(delta_speed) >= 5;

Deteksi Pengapian & Idle

Mengukur waktu idle (ignition menyala, kecepatan rendah/tidak ada) membantu mengurangi pemborosan bahan bakar dan mengidentifikasi penyalahgunaan. Periode idling yang lama harus dilaporkan dan dikelola.

Dashboard Studio dan alat serupa tidak mendukung substitusi variabel (:variable sintaks). Ganti semua parameter dengan nilai literal sebelum menjalankan kueri ini. Lihat contoh di bawah untuk format yang benar.

WITH ign AS (
    SELECT
        device_id,
        device_time,
        value::int AS ign_on
    FROM raw_telematics_data.states
    WHERE state_name = 'ignition'
      AND device_time BETWEEN :from_ts::timestamptz AND :to_ts::timestamptz
      -- Untuk Dashboard Studio, ganti :from_ts dan :to_ts dengan stempel waktu literal:
      -- TIMESTAMPTZ '2024-01-01 00:00:00+00' AND TIMESTAMPTZ '2024-01-07 00:00:00+00'
),
spd AS (
    SELECT
        device_id,
        device_time,
        speed / 100.0 AS kmh
    FROM raw_telematics_data.tracking_data_core
    WHERE device_time BETWEEN :from_ts AND :to_ts
    -- Untuk Dashboard Studio, ganti :from_ts dan :to_ts dengan stempel waktu literal:
    -- TIMESTAMPTZ '2024-01-01 00:00:00+00' AND TIMESTAMPTZ '2024-01-07 00:00:00+00'
),
merged AS (
    SELECT
        i.device_id,
        i.device_time,
        i.ign_on,
        s.kmh,
        LEAD(i.device_time) OVER (
            PARTITION BY i.device_id
            ORDER BY i.device_time
        ) AS next_time
    FROM ign i
    LEFT JOIN spd s USING (device_id, device_time)
)
SELECT
    device_id,
    device_time AS idle_start,
    next_time   AS idle_end,
    EXTRACT(EPOCH FROM (next_time - device_time)) / 60.0 AS idle_minutes
FROM merged
WHERE ign_on = 1
  AND kmh < :idle_speed
  -- Untuk Dashboard Studio, ganti :idle_speed dengan nilai numerik, misalnya, 5
  AND next_time IS NOT NULL
  AND (next_time - device_time) >= (:idle_min::int * INTERVAL '1 minute');
  -- Untuk Dashboard Studio, ganti :idle_min dengan nilai numerik, misalnya, (5 * INTERVAL '1 minute')