How to Calculate the Average Time Between Purchases in SQL
This query calculates the average time between consecutive purchases for each customer.
WITH
purchase_dates AS (
SELECT
customer_id,
purchase_date,
LAG (purchase_date) OVER (
PARTITION BY
customer_id
ORDER BY
purchase_date
) AS previous_purchase_date
FROM
purchases
)
SELECT
customer_id,
AVG(purchase_date-previous_purchase_date) AS avg_time_between_purchases
FROM
purchase_dates
WHERE
previous_purchase_date IS NOT NULL
GROUP BY
customer_id;
Use-case: A customer success team wants to understand purchasing patterns to improve customer retention strategies.
Ready to build Dashboards
and set Alerts?
This website uses cookies to ensure you get the best experience.