Unable to put dates in a column and metric in another in SQL

user_11683378 0
Viewed 109

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

metrics join date mysql sql
2 Answers

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;
  • I change the curdate() from your query to the value date.d.
  • select * from date should give all dates, at least from the last 61 days.
user_7240390

You want to get percentage on OTD for all the dates in dates table where and get the metrics for respective days. So percentage is calculated as : ((no of on time deliveries between last 30 days and day before the date) / (total no of deliveries between last 30 days and day before the date))*100;

So if you took example of date 2020-06-02 there are total 3 deliveries happened between 2020-05-02 to 2020-06-01 out of which 1 delivery is on time therefore OTD percentage = 33.3

According to what i have understood, Here is a solution you may be looking for:

Please check SQL fiddle:

SELECT d.fulldate,sum(case when promise_date = date(delivered_at) 
                      and p.seller_id = 123 and date(created_at) between DATE_SUB(d.fulldate,interval 30 day) and 
DATE_SUB(d.fulldate,interval 1 day) then 1 else 0 end)/sum(case when date(created_at) between DATE_SUB(d.fulldate,interval 30 day) and 
DATE_SUB(d.fulldate,interval 1 day) and p.seller_id = 123 then 1 else 0 end)*100
"On-Time Delivery Rate (%)"
from packages p, dates d group by d.fulldate;
where p.seller_id=123 and date(created_at) between DATE_SUB(d.fulldate,interval 30 day) and 
DATE_SUB(d.fulldate,interval 1 day);

Output:

Below is partial output: (for full output you can check Fiddle given above the query)

fulldate    On-Time Delivery Rate (%)
2020-06-02  33.3333
2020-06-03  33.3333
2020-06-04  50
2020-06-05  50
2020-06-06  50
2020-06-07  50
user_107404200
Related