By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
doctor VARCHAR(255),
patient VARCHAR(255),
bought_x_days_ago INT);
INSERT INTO mytable VALUES
('Aaron','Jeff',10),
('Aaron','Jeff',20),
('Jess','Jason',50),
('Jess','Jason',20),
('Jess','Jason',30),
('Aaron','Stu',90),
('Aaron','Stu',70),
('Aaron','Stu',110),
('Aaron','Stu',105);
SELECT m1.doctor,
m1.patient,
m1.bought_x_days_ago AS bxd,
GROUP_CONCAT(m2.bought_x_days_ago ORDER BY m2.bought_x_days_ago) AS prev_bxd
FROM mytable m1
LEFT JOIN mytable m2
ON m1.doctor=m2.doctor
AND m1.patient=m2.patient
AND m1.bought_x_days_ago < m2.bought_x_days_ago
GROUP BY
m1.doctor,
m1.patient,
m1.bought_x_days_ago;
SELECT m1.doctor,
m1.patient,
m1.bought_x_days_ago AS bxd,
SUBSTRING_INDEX(
GROUP_CONCAT(m2.bought_x_days_ago ORDER BY m2.bought_x_days_ago),',',1) AS prev_bxd
FROM mytable m1
LEFT JOIN mytable m2
ON m1.doctor=m2.doctor
AND m1.patient=m2.patient
AND m1.bought_x_days_ago < m2.bought_x_days_ago
GROUP BY
m1.doctor,
m1.patient,
m1.bought_x_days_ago
| doctor | patient | bxd | prev_bxd |
|---|---|---|---|
| Aaron | Jeff | 10 | 20 |
| Aaron | Jeff | 20 | null |
| Aaron | Stu | 70 | 90,105,110 |
| Aaron | Stu | 90 | 105,110 |
| Aaron | Stu | 105 | 110 |
| Aaron | Stu | 110 | null |
| Jess | Jason | 20 | 30,50 |
| Jess | Jason | 30 | 50 |
| Jess | Jason | 50 | null |
| doctor | patient | bxd | prev_bxd |
|---|---|---|---|
| Aaron | Jeff | 10 | 20 |
| Aaron | Jeff | 20 | null |
| Aaron | Stu | 70 | 90 |
| Aaron | Stu | 90 | 105 |
| Aaron | Stu | 105 | 110 |
| Aaron | Stu | 110 | null |
| Jess | Jason | 20 | 30 |
| Jess | Jason | 30 | 50 |
| Jess | Jason | 50 | null |
SELECT doctor,
patient,
AVG(prev_bxd-bxd)
FROM
(SELECT m1.doctor,
m1.patient,
m1.bought_x_days_ago AS bxd,
SUBSTRING_INDEX(
GROUP_CONCAT(m2.bought_x_days_ago ORDER BY m2.bought_x_days_ago),',',1) AS prev_bxd
FROM mytable m1
LEFT JOIN mytable m2
ON m1.doctor=m2.doctor
AND m1.patient=m2.patient
AND m1.bought_x_days_ago < m2.bought_x_days_ago
GROUP BY
m1.doctor,
m1.patient,
m1.bought_x_days_ago) v
GROUP BY doctor,
patient
| doctor | patient | AVG(prev_bxd-bxd) |
|---|---|---|
| Aaron | Jeff | 10 |
| Aaron | Stu | 13.333333333333334 |
| Jess | Jason | 15 |