Question: For a given day t, I need to create an on-time delivery (OTD) metric for 61 days for seller_id "123".
The OTD is the rate at which packages were delivered on-time
for packages created in the 30 days preceding t.
Not only do we want to know today's OTD, but we want to know the OTD for
every date in the past 60 days.
CREATE TABLE `packages` (
`id` int NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`seller_id` int DEFAULT NULL,
`promise_date` date DEFAULT NULL,
`delivered_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `packages` (`id`, `created_at`, `seller_id`, `promise_date`, `delivered_at`) VALUES
('1', '2020-04-15 06:26:28', '246', '2020-04-20', '2020-04-20 07:47:07'),
('2', '2020-05-02 06:19:13', '123', '2020-05-06', '2020-05-07 07:01:26'),
('3', '2020-07-05 08:47:17', '789', '2020-07-08', '2020-07-09 08:22:08'),
('4', '2020-03-25 08:12:35', '234', '2020-03-30', '2020-03-30 08:49:50'),
('5', '2020-07-01 07:51:33', '789', '2020-07-06', '2020-07-06 08:34:48'),
('6', '2020-03-01 08:11:31', '123', '2020-03-04', '2020-03-04 06:00:14'),
('7', '2020-05-20 07:38:14', '246', '2020-05-25', '2020-05-25 08:13:51'),
('8', '2020-04-14 07:30:19', '123', '2020-04-17', '2020-04-17 07:46:55'),
('9', '2020-02-10 08:53:00', '234', '2020-02-13', '2020-02-14 06:45:57'),
('10', '2020-01-21 07:14:56', '246', '2020-01-24', '2020-01-25 08:03:04'),
('11', '2020-07-03 08:05:11', '123', '2020-07-08', '2020-07-09 08:30:56'),
('12', '2020-05-09 06:18:31', '789', '2020-05-13', '2020-05-13 08:38:55'),
('13', '2020-02-13 08:11:10', '123', '2020-02-18', '2020-02-18 07:48:52'),
('14', '2020-04-28 08:25:28', '789', '2020-05-01', '2020-05-02 06:06:32'),
('15', '2020-06-02 07:28:52', '234', '2020-06-05', '2020-06-06 07:29:43'),
('16', '2020-05-04 08:39:33', '123', '2020-05-07', '2020-05-08 06:33:14'),
('17', '2020-07-26 08:18:30', '789', '2020-07-29', '2020-07-30 07:28:53'),
('18', '2020-02-25 08:37:42', '234', '2020-02-28', '2020-02-29 06:05:23'),
('19', '2020-02-03 06:55:39', '234', '2020-02-06', '2020-02-07 07:18:28'),
('20', '2020-03-07 08:20:44', '246', '2020-03-11', '2020-03-11 08:11:45'),
('21', '2020-03-11 07:19:47', '789', '2020-03-16', '2020-03-16 06:55:46'),
('22', '2020-06-24 08:18:56', '789', '2020-06-29', '2020-06-29 08:47:59'),
('23', '2020-02-25 07:24:19', '123', '2020-02-28', '2020-02-28 06:54:57'),
('24', '2020-07-12 07:51:52', '789', '2020-07-15', '2020-07-16 07:36:21'),
('25', '2020-01-26 07:44:59', '234', '2020-01-29', '2020-01-29 08:52:24'),
('26', '2020-02-07 06:09:24', '246', '2020-02-12', '2020-02-13 08:16:37'),
('27', '2020-03-11 08:34:57', '123', '2020-03-16', '2020-03-17 08:33:47'),
('28', '2020-02-24 08:15:41', '789', '2020-02-27', '2020-02-27 06:19:59'),
('29', '2020-02-02 06:45:36', '123', '2020-02-05', '2020-02-06 06:22:25'),
('30', '2020-02-10 06:51:48', '123', '2020-02-13', '2020-02-13 06:45:07'),
('31', '2020-03-27 07:11:58', '789', '2020-04-01', '2020-04-02 08:55:56'),
('32', '2020-05-31 07:10:05', '246', '2020-06-03', '2020-06-03 08:56:47'),
('33', '2020-06-28 06:14:19', '789', '2020-07-01', '2020-07-02 06:35:18'),
('34', '2020-07-08 08:30:12', '789', '2020-07-13', '2020-07-14 06:06:09'),
('35', '2020-05-13 08:13:34', '123', '2020-05-18', '2020-05-18 08:24:42'),
('36', '2020-04-19 08:13:38', '246', '2020-04-22', '2020-04-22 07:32:14'),
('37', '2020-03-02 06:57:32', '234', '2020-03-05', '2020-03-05 07:16:05'),
('38', '2020-05-22 08:49:51', '246', '2020-05-27', '2020-05-27 06:47:41'),
('39', '2020-02-27 08:18:26', '123', '2020-03-03', '2020-03-03 06:32:56'),
('40', '2020-02-17 07:10:24', '246', '2020-02-20', '2020-02-21 06:06:26'),
('41', '2020-06-25 08:29:32', '234', '2020-06-30', '2020-06-30 07:37:07'),
('42', '2020-03-02 08:07:57', '234', '2020-03-05', '2020-03-05 08:41:13'),
('43', '2020-06-18 06:44:38', '234', '2020-06-23', '2020-06-23 06:11:26'),
('44', '2020-07-15 08:22:49', '246', '2020-07-20', '2020-07-20 08:34:28'),
('45', '2020-07-07 07:54:10', '123', '2020-07-10', '2020-07-10 07:50:24'),
('46', '2020-07-17 07:43:08', '123', '2020-07-22', '2020-07-22 06:33:22'),
('47', '2020-04-01 08:24:20', '234', '2020-04-06', '2020-04-06 06:12:55'),
('48', '2020-05-14 08:49:10', '123', '2020-05-19', '2020-05-20 06:53:50'),
('49', '2020-06-11 08:20:35', '246', '2020-06-16', '2020-06-16 06:21:10'),
('50', '2020-06-24 06:39:29', '789', '2020-06-29', '2020-06-30 06:03:48'),
('51', '2020-02-29 06:43:01', '246', '2020-03-04', '2020-03-05 07:57:51'),
('52', '2020-07-17 08:23:46', '246', '2020-07-22', '2020-07-22 07:49:01'),
('53', '2020-03-27 07:45:10', '123', '2020-04-01', '2020-04-02 06:43:34'),
('54', '2020-04-28 06:39:55', '246', '2020-05-01', '2020-05-01 08:59:08'),
('55', '2020-05-21 07:16:03', '789', '2020-05-26', '2020-05-26 06:29:53'),
('56', '2020-02-10 08:22:04', '246', '2020-02-13', '2020-02-14 06:24:17'),
('57', '2020-02-02 08:04:26', '234', '2020-02-05', '2020-02-05 08:59:43'),
('58', '2020-03-02 08:21:53', '246', '2020-03-05', '2020-03-06 08:45:36'),
('59', '2020-02-19 08:37:15', '123', '2020-02-24', '2020-02-24 08:24:44'),
('60', '2020-06-16 08:51:24', '234', '2020-06-19', '2020-06-20 08:25:14'),
('61', '2020-07-11 07:37:15', '234', '2020-07-15', '2020-07-15 07:03:13'),
('62', '2020-06-15 07:56:39', '123', '2020-06-18', '2020-06-19 07:11:16'),
('63', '2020-03-06 07:21:52', '123', '2020-03-11', '2020-03-11 07:46:48'),
('64', '2020-06-03 06:43:50', '789', '2020-06-08', '2020-06-09 07:40:17'),
('65', '2020-01-20 06:28:47', '234', '2020-01-23', '2020-01-24 08:34:05'),
('66', '2020-04-02 08:04:41', '123', '2020-04-07', '2020-04-08 08:56:45'),
('67', '2020-03-04 06:05:57', '789', '2020-03-09', '2020-03-10 06:26:56'),
('68', '2020-07-04 06:47:46', '246', '2020-07-08', '2020-07-09 06:53:02'),
('69', '2020-02-25 06:47:09', '246', '2020-02-28', '2020-02-28 07:55:25'),
('70', '2020-02-04 07:17:28', '123', '2020-02-07', '2020-02-07 08:07:54'),
('71', '2020-06-15 07:18:16', '789', '2020-06-18', '2020-06-19 06:02:08'),
('72', '2020-07-09 06:32:34', '234', '2020-07-14', '2020-07-14 08:15:02'),
('73', '2020-05-21 06:12:52', '789', '2020-05-26', '2020-05-27 07:39:20'),
('74', '2020-05-24 06:38:49', '789', '2020-05-27', '2020-05-27 06:51:35'),
('75', '2020-02-27 06:31:02', '123', '2020-03-03', '2020-03-03 08:56:26'),
('76', '2020-07-02 08:55:00', '123', '2020-07-07', '2020-07-07 07:42:16'),
('77', '2020-06-30 06:52:27', '246', '2020-07-03', '2020-07-03 07:43:20'),
('78', '2020-04-25 08:08:14', '246', '2020-04-29', '2020-04-29 07:21:23'),
('79', '2020-06-24 08:34:43', '234', '2020-06-29', '2020-06-30 06:43:59'),
('80', '2020-05-13 08:59:11', '246', '2020-05-18', '2020-05-18 07:19:06'),
('81', '2020-02-21 07:14:16', '789', '2020-02-26', '2020-02-27 07:10:39'),
('82', '2020-06-04 08:43:13', '789', '2020-06-09', '2020-06-09 07:24:28'),
('83', '2020-07-04 07:14:42', '234', '2020-07-08', '2020-07-09 07:45:59'),
('84', '2020-05-24 08:17:00', '246', '2020-05-27', '2020-05-27 06:31:15'),
('85', '2020-03-07 07:43:27', '123', '2020-03-11', '2020-03-12 08:39:45');
create table dates(
fulldate date);
INSERT INTO `dates` (`fulldate`) VALUES
('2020-08-01'),
('2020-07-31'),
('2020-07-30'),
('2020-07-29'),
('2020-07-28'),
('2020-07-27'),
('2020-07-26'),
('2020-07-25'),
('2020-07-24'),
('2020-07-23'),
('2020-07-22'),
('2020-07-21'),
('2020-07-20'),
('2020-07-19'),
('2020-07-18'),
('2020-07-17'),
('2020-07-16'),
('2020-07-15'),
('2020-07-14'),
('2020-07-13'),
('2020-07-12'),
('2020-07-11'),
('2020-07-10'),
('2020-07-09'),
('2020-07-08'),
('2020-07-07'),
('2020-07-06'),
('2020-07-05'),
('2020-07-04'),
('2020-07-03'),
('2020-07-02'),
('2020-07-01'),
('2020-06-30'),
('2020-06-29'),
('2020-06-28'),
('2020-06-27'),
('2020-06-26'),
('2020-06-25'),
('2020-06-24'),
('2020-06-23'),
('2020-06-22'),
('2020-06-21'),
('2020-06-20'),
('2020-06-19'),
('2020-06-18'),
('2020-06-17'),
('2020-06-16'),
('2020-06-15'),
('2020-06-14'),
('2020-06-13'),
('2020-06-12'),
('2020-06-11'),
('2020-06-10'),
('2020-06-09'),
('2020-06-08'),
('2020-06-07'),
('2020-06-06'),
('2020-06-05'),
('2020-06-04'),
('2020-06-03'),
('2020-06-02');
Desired Output:
date | on_time_delivery_ratio
2020-07-31 | 0.75
2020-07-30 | 0.69
2020-07-29 | 0.68
2020-07-28 | 0.80
2020-07-27 | 0.79
2020-07-26 | 0.78
2020-07-25 | 0.69
2020-07-24 | 0.72
What I have done:
I have been able to create a metric, however just for the current date.
SELECT curdate(),sum(case when promise_date=date(delivered_at) then 1 else 0 end)/count(*)*100
"On-Time Delivery Rate (%)"
from packages p
where p.seller_id=123 and date(created_at) between DATE_SUB(curdate(),interval 30 day) and
DATE_SUB(curdate(),interval 1 day);
Where I need help:
Instead of using curdate(), I need the dates from the dates table as the first column of the desired output and the metric for those dates.
SQL Fiddle: http://sqlfiddle.com/#!9/b665ca1/2
Fiddle: http://sqlfiddle.com/#!9/b665ca1/7 (based on http://sqlfiddle.com/#!9/b665ca1/2 )
SELECT
delivered_at,
sum(case when promise_date=date(delivered_at) then 1 else 0 end)/count(*) "On-Time Delivery Rate (%)"
from packages p
where p.seller_id=123
and date(delivered_at) between DATE_SUB(curdate(),interval 30 day) and DATE_SUB(curdate(),interval 1 day)
group by delivered_at;
output:
delivered_at On-Time Delivery Rate (%)
2020-07-07T07:42:16Z 1
2020-07-09T08:30:56Z 0
2020-07-10T07:50:24Z 1
2020-07-22T06:33:22Z 1
EDIT: to select the last 61 days
SELECT date.d,sum(case when promise_date=date(delivered_at) then 1 else 0 end)/count(*)*100
"On-Time Delivery Rate (%)"
from date
CROSS JOIN packages p
where p.seller_id=123
and date(created_at) between DATE_SUB(date.d,interval 30 day) and DATE_SUB(date.d,interval 1 day)
and date.d >= DATE_SUB(curdate(),interval 61 day)
GROUP BY date.d
ORDER BY date.d;
curdate() from your query to the value date.d.select * from date should give all dates, at least from the last 61 days.