PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – query based on previous lines values: values that open the 30 days windows (and are not inside them)

I have a service in which customers can open a "customer service ticket" which is charged. Whenever they do, they can open how many more "customer service tickets" they want for the next 30 days without being charged; and after that period if they open a ticket again they will be charged again (and if that happens, they will have another 30 days of free customer service).

Based on that, I have a table called customer_service_ticket in which I store each ticket with its date on "date" column.

What query gets all tickets which are chargeable, that is, these tickets that open the 30 days windows (and not tickets inside them)?

Eg. If the tickets have the dates
3/jan – 4/jan – 8/jan – 30/jan – 4/fev – 20/fev – 20/mar

the query should only return
3/jan – 4/fev – 20/mar.

Because every statement is applied to all lines, I couldn’t find a solution. I am working on a recursive CTE.

Tags:

2

Answers


  1. 0

    You’re absolutely right; using recursive CTE might be a bit overcomplicated for this particular scenario. Instead, a combination of window functions and subqueries can efficiently achieve the desired result. The approach involves identifying the start date of each 30-day window and selecting only those tickets.

    Assuming your table is named customer_service_ticket and includes columns such as ticket_id and date, the following query should provide the desired output:

    WITH TicketWithPreviousDate AS (
    SELECT
        date,
        LAG(date) OVER (ORDER BY date) AS previous_date
    FROM
        customer_service_ticket
)


SELECT
    date
FROM
    TicketWithPreviousDate
WHERE
    previous_date IS NULL OR
    date >= DATE_ADD(previous_date, INTERVAL 30 DAY)
ORDER BY
    date;

    This query returns the dates of customer service tickets that mark the beginning of a new 30-day period, during which customers can open additional "customer service tickets" without being charged.

    Login or Signup to reply.
  2. 0

    A recursive CTE is required for this type of problem because the selected rows are dependent on prior selected rows.

    The following demonstrates a query that performs the requested operation:

    WITH RECURSIVE
  tickets(ticket_id, customer_id, ticket_date) AS (
    VALUES (1, 1, '2023-01-03'::date),
           (2, 1, '2023-01-04'::date),
           (3, 1, '2023-01-30'::date),
           (4, 1, '2023-02-04'::date),
           (5, 1, '2023-02-20'::date),
           (6, 1, '2023-03-20'::date)
  ),
  parms AS (
    SELECT 30 AS support_days
  ),
  charged_tickets AS (
    SELECT * FROM (SELECT DISTINCT ON (t.customer_id) t.customer_id,
                          t.ticket_id,
                          t.ticket_date,
                          t.ticket_date + p.support_days AS next_chargeable_start
                     FROM parms p
                     CROSS JOIN tickets t
                     ORDER BY t.customer_id, t.ticket_date, t.ticket_id) first_charged_tickets
    UNION ALL
    SELECT * FROM (SELECT DISTINCT ON (t.customer_id) t.customer_id,
                          t.ticket_id,
                          t.ticket_date,
                          t.ticket_date + p.support_days AS next_chargeable_start
                     FROM parms p
                     CROSS JOIN tickets t
                     JOIN charged_tickets
                       ON t.customer_id = charged_tickets.customer_id
                         AND t.ticket_date >= charged_tickets.next_chargeable_start
                     ORDER BY t.customer_id, t.ticket_date, t.ticket_id) next_charged_tickets
  )
SELECT ct.customer_id, ct.ticket_id, ct.ticket_date
  FROM charged_tickets ct
  ORDER BY ct.customer_id, ct.ticket_date;

    Results:

    customer_id ticket_id ticket_date
    1 1 2023-01-03
    1 4 2023-02-04
    1 6 2023-03-20

    The query begins by establishing example data as tickets (this would be an actual table for production use). The number of support days is defined in parms. This allows the query to be more DRY (Don’t Repeat Yourself).

    The recursive CTE is found in charged_tickets. The initialization section determines the first charged ticket for each customer and the next date that charges will again apply. The iterative part performs the same operatiion for each successive charged ticket. The subqueries in charged_tickets are necessary because PostgreSQL doesn’t support DISTINCT ON directly in recursive subqueries.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search