-- Ikki yoki undan ortiq kun davomida ketma-ket sayohat qilgan barcha yo'lovchilarni topish

Ikki yoki undan ortiq ketma-ket kunlarda sayohat qilgan yo'lovchilarni topish uchun SQL bilan oynali funksiyalar yoki guruhlashlardan foydalanish mumkin.

SQL misoli (trips jadvali passenger_id va trip_date ustunlariga ega deb faraz qilamiz):

WITH ordered_trips AS (
  SELECT
    passenger_id,
    trip_date,
    LAG(trip_date) OVER (PARTITION BY passenger_id ORDER BY trip_date) AS prev_date
  FROM trips
), consecutive_days AS (
  SELECT
    passenger_id,
    trip_date,
    prev_date,
    CASE WHEN trip_date = DATE_ADD(prev_date, INTERVAL 1 DAY) THEN 1 ELSE 0 END AS is_consecutive
  FROM ordered_trips
), flagged AS (
  SELECT passenger_id
  FROM consecutive_days
  WHERE is_consecutive = 1
  GROUP BY passenger_id
)
SELECT DISTINCT passenger_id FROM flagged;

Mantiq:

• Har bir yo'lovchi uchun sayohatlar sanasi bo'yicha tartiblanadi.
• Hozirgi sana oldingi sana bilan solishtiriladi, shunda kunlar ketma-ket yoki yo'qligi aniqlanadi.
• Agar kamida bir juft ketma-ket kun bo'lsa, yo'lovchi natijaga qo'shiladi.