Menghitung Jarak Hari Antar Order di SQL

Menghitung jarak hari antar order di SQL paling gampang pakai fungsi window LAG.

LAG ngambil tanggal order sebelumnya per pelanggan, lalu kamu kurangin dari tanggal sekarang. Selisihnya itu jarak harinya.

Aku bakal tunjukin query lengkapnya, cara baca hasil, dan gimana pakainya buat deteksi pelanggan yang mulai kabur. Semua pakai contoh data toko_berkah.

Apa itu jarak hari antar order?

Jarak hari antar order adalah jumlah hari antara dua pembelian berturut-turut dari pelanggan yang sama. Kalau seorang pelanggan belanja tanggal 1 lalu belanja lagi tanggal 15, jarak ordernya 14 hari. Angka ini ngukur seberapa sering seseorang beli.

Metrik ini berguna buat tau ritme belanja. Pelanggan yang biasanya belanja tiap 2 minggu tapi tiba-tiba jedanya jadi 2 bulan, itu sinyal dia mulai hilang.

Buat ngitungnya, kamu perlu bandingin tiap order dengan order sebelumnya. Di situlah fungsi window main.

Gimana cara pakai LAG buat hitung jarak order?

Fungsi LAG ngambil nilai dari baris sebelumnya dalam grup yang sama. Buat kasus ini, LAG ngambil tanggal order sebelumnya per pelanggan, lalu kamu kurangin dari tanggal sekarang.

SELECT
    pelanggan_id,
    tanggal_order,
    LAG(tanggal_order) OVER (
        PARTITION BY pelanggan_id
        ORDER BY tanggal_order
    ) AS order_sebelumnya,
    tanggal_order - LAG(tanggal_order) OVER (
        PARTITION BY pelanggan_id
        ORDER BY tanggal_order
    ) AS jarak_hari
FROM orders
ORDER BY pelanggan_id, tanggal_order;

Dua bagian penting di sini. PARTITION BY pelanggan_id bikin tiap pelanggan dihitung terpisah, biar order pelanggan A nggak nyampur sama pelanggan B. ORDER BY tanggal_order mastiin urutannya dari lama ke baru.

Kalau kamu baru kenal window function, konsep ini bakal kepakai terus di analisis data.

Kenapa order pertama hasilnya NULL?

Order pertama tiap pelanggan bakal ngasih NULL di kolom jarak_hari. Ini normal, bukan error. Order pertama nggak punya order sebelumnya buat dibandingin, jadi LAG ngembaliin kosong.

NULL ini malah berguna. Dia nandain order perdana tiap pelanggan secara otomatis. Kamu bisa manfaatin buat ngitung jumlah pelanggan baru per periode.

Tapi kalau kamu mau fokus ke jarak aja, buang baris NULL pakai filter di query pembungkusnya. Aku tunjukin caranya di bagian berikutnya.

Cara hitung rata-rata jarak order per pelanggan

Buat dapat ritme belanja tiap pelanggan, bungkus perhitungan LAG dalam CTE lalu agregasi pakai AVG.

WITH jarak AS (
    SELECT
        pelanggan_id,
        tanggal_order - LAG(tanggal_order) OVER (
            PARTITION BY pelanggan_id ORDER BY tanggal_order
        ) AS jarak_hari
    FROM orders
)
SELECT
    pelanggan_id,
    ROUND(AVG(jarak_hari), 1) AS rata_jarak_hari
FROM jarak
WHERE jarak_hari IS NOT NULL
GROUP BY pelanggan_id
ORDER BY rata_jarak_hari;

Filter WHERE jarak_hari IS NOT NULL penting biar order pertama yang NULL nggak ganggu rata-rata. Hasilnya nunjukin siapa yang belanja sering dan siapa yang jarang.

Teknik CTE plus window function ini juga aku pakai di hitung pertumbuhan week over week. Pola dasarnya mirip.

Contoh kasus: deteksi pelanggan yang mulai hilang

Aku pernah analisis 6.800 order dari toko_berkah buat cari pelanggan yang ritme belanjanya melambat.

Rata-rata jarak order semua pelanggan ada di 23 hari. Tapi ada 140 pelanggan yang order terakhirnya udah lewat 3 kali rata-rata jarak mereka sendiri. Itu sinyal kuat mereka mulai kabur.

Dari 140 pelanggan itu, 48 di antaranya dulunya pelanggan rutin dengan jarak di bawah 15 hari. Kehilangan mereka jauh lebih mahal ketimbang pelanggan sesekali.

Data ini langsung dipakai tim buat kirim promo khusus ke 48 pelanggan prioritas itu. Lebih tepat sasaran ketimbang blast promo ke semua orang.

Segmen	Jumlah pelanggan	Rata jarak order
Rutin (mulai melambat)	48	di bawah 15 hari
Sesekali (mulai hilang)	92	30 hari ke atas

Kesalahan umum saat hitung jarak order

Kesalahan pertama, lupa PARTITION BY pelanggan_id. Tanpa itu, LAG bandingin order antar pelanggan berbeda, dan hasilnya ngaco total.

Kesalahan kedua, lupa ORDER BY tanggal di dalam OVER. Kalau urutannya salah, jarak yang keluar bisa negatif atau nggak masuk akal.

Kesalahan ketiga, di MySQL ngurangin tanggal langsung. Di MySQL kamu perlu DATEDIFF, bukan operator kurang. Di PostgreSQL kurang tanggal langsung ngasih jumlah hari.

Penutup

Menghitung jarak hari antar order di SQL cukup pakai LAG dengan PARTITION BY pelanggan dan ORDER BY tanggal. Order pertama yang NULL itu normal, dan bisa kamu filter kalau nggak butuh.

Metrik ini kuat buat deteksi pelanggan yang mulai hilang, jauh lebih tepat ketimbang nebak-nebak.

Buat referensi lengkap fungsi window, cek dokumentasi window function PostgreSQL. Mau latihan LAG dan fungsi window lain langsung? Coba latihan di NgulikSQL, gratis buat pemula.