You didn't specify what RDBMS is this. So let me assume that it is SQL Server, and you can do this:
;WITH MinDates AS
(
SELECT
produc_id,
MIN(order_date) order_date
FROM products
GROUP BY produc_id
), DatesWithIntervals AS
(
SELECT
product_id,
order_date,
CASE
WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 31 THEN 1
WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 365 THEN 2
WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 365 * 2 THEN 3
ELSE 4
END "Interval"
FROM MinDates
)
SELECT
product_id,
order_date
...
FROM DatesWithIntervals
--Do your statics here
You didn't specify what statistics do you want to compute. But you should be able to do whatever statistics you want to do using the last cte: DatesWithIntervals
depending on the field Interval
and I will leave it as a practice for you.
Edit: For MySQL, just replace all these CTE
s with subqueries like so:
SELECT
product_id,
order_date
...
FROM
(
SELECT
product_id,
order_date,
CASE
WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 31 THEN 1
WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 365 THEN 2
WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 365 * 2 THEN 3
ELSE 4
END "Interval"
FROM
(
SELECT
produc_id,
MIN(order_date) order_date
FROM products
GROUP BY produc_id
) MinDates
) DatesWithIntervals
--Do your statics here