SQL interview question: Find the sum of time differences (ignore duplicates)

SQL interview question: Find the sum of time differences (ignore duplicates)

When I was interviewing for a BI position at a certain company, there was a SQL question in the interview. It seemed very simple at first glance, but when I was writing it, I found that I lacked summary and could not write it out quickly.

The topics are as follows:

Find the number of promotion days for each brand

Table sale is a promotional marketing table. There are repeated dates in the data. For example, the end_date of id 1 is 20180905, and the start_date of id 2 is 20180903. That is, id 1 and id 2 have repeated sales dates. Find the number of promotion days for each brand (duplicates are not counted)

The table results are as follows:

+------+-------+------------+------------+
| id | brand | start_date | end_date |
+------+-------+------------+------------+
| 1 | nike | 2018-09-01 | 2018-09-05 |
| 2 | nike | 2018-09-03 | 2018-09-06 |
| 3 | nike | 2018-09-09 | 2018-09-15 |
| 4 | oppo | 2018-08-04 | 2018-08-05 |
| 5 | oppo | 2018-08-04 | 2018-08-15 |
| 6 | vivo | 2018-08-15 | 2018-08-21 |
| 7 | vivo | 2018-09-02 | 2018-09-12 |
+------+-------+------------+------------+

The final result should be

brand all_days
Nike 13
OPPO 12
vivo 18

Create table statement

-- ----------------------------
-- Table structure for sale
-- ----------------------------
DROP TABLE IF EXISTS `sale`;
CREATE TABLE `sale` (
 `id` int(11) DEFAULT NULL,
 `brand` varchar(255) DEFAULT NULL,
 `start_date` date DEFAULT NULL,
 `end_date` date DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sale
-- ----------------------------
INSERT INTO `sale` VALUES (1, 'nike', '2018-09-01', '2018-09-05');
INSERT INTO `sale` VALUES (2, 'nike', '2018-09-03', '2018-09-06');
INSERT INTO `sale` VALUES (3, 'nike', '2018-09-09', '2018-09-15');
INSERT INTO `sale` VALUES (4, 'oppo', '2018-08-04', '2018-08-05');
INSERT INTO `sale` VALUES (5, 'oppo', '2018-08-04', '2018-08-15');
INSERT INTO `sale` VALUES (6, 'vivo', '2018-08-15', '2018-08-21');
INSERT INTO `sale` VALUES (7, 'vivo', '2018-09-02', '2018-09-12');

Method 1:

Using the method of self-association to the next record

select brand,sum(end_date-befor_date+1) all_days from 
 (
 select s.id ,
  s.brand,
  s.start_date ,
  s.end_date , 
  if(s.start_date>=ifnull(t.end_date,s.start_date) ,s.start_date,DATE_ADD(t.end_date,interval 1 day) ) as before_date
 from sale s left join (select id+1 as id ,brand,end_date from sale) t on s.id = t.id and s.brand = t.brand
 order by s.id
 )tmp
 group by brand

Operation Results

+-------+---------+
| brand | all_day |
+-------+---------+
| nike | 13 |
| oppo | 12 |
| vivo | 18 |
+-------+---------+

This method is valid for the table in this question, but may not be applicable to records of brands with discontinuous ids.

Method 2:

SELECT a.brand,SUM(
 CASE 
  WHEN a.start_date=b.start_date AND a.end_date=b.end_date
  AND NOT EXISTS(
  SELECT *
  FROM sale c LEFT JOIN sale d ON c.brand=d.brand 
   WHERE d.brand = a.brand
   AND c.start_date=a.start_date
   AND c.id<>d.id 
   AND (d.start_date BETWEEN c.start_date AND c.end_date AND d.end_date>c.end_date
   OR 
  c.start_date BETWEEN d.start_date AND d.end_date AND c.end_date>d.end_date)
    ) 
   THEN (a.end_date-a.start_date+1) 
  WHEN (a.id<>b.id AND b.start_date BETWEEN a.start_date AND a.end_date AND b.end_date>a.end_date ) THEN (b.end_date-a.start_date+1)
  ELSE 0 END
  ) AS all_days 
FROM sale a JOIN sale b ON a.brand=b.brand GROUP BY a.brand

Operation Results

+-------+----------+
| brand | all_days |
+-------+----------+
| nike | 13 |
| oppo | 12 |
| vivo | 18 |
+-------+----------+

Among the conditions

d.start_date BETWEEN c.start_date AND c.end_date AND d.end_date>c.end_date
   OR 
c.start_date BETWEEN d.start_date AND d.end_date AND c.end_date>d.end_date

Can be replaced with

c.start_date < d.end_date AND (c.end_date > d.start_date)

The result is also correct

It is also feasible to use analytical functions. I don’t have Oracle installed on my computer yet, so I wrote it in MySQL.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:
  • Detailed explanation of how to calculate time difference in PHP and MySql
  • mysql calculate time difference function
  • Method to calculate time difference in php and MySql

<<:  Summary of common commands for Linux user and group management

>>:  How to draw the timeline with vue+canvas

Recommend

How to view the creation time of files in Linux

1. Introduction Whether the creation time of a fi...

Several common ways to deploy Tomcat projects [tested]

1 / Copy the web project files directly to the we...

MySQL database master-slave replication and read-write separation

Table of contents 1. Master-slave replication Mas...

Bootstrap 3.0 study notes page layout

This time we will mainly learn about layout, whic...

VUE introduces the implementation of using G2 charts

Table of contents About G2 Chart use Complete cod...

How to Rename a Group of Files at Once on Linux

In Linux, we usually use the mv command to rename...

61 Things Every Web Developer Should Know

Normally, you'll need to read everyone's s...

Detailed explanation of object literals in JS

Table of contents Preface 1. Set the prototype on...

Fabric.js implements DIY postcard function

This article shares the specific code of fabricjs...

Linux operation and maintenance basic swap partition and lvm management tutorial

Table of contents 1. Swap partition SWAP 1.1 Crea...

Optimized implementation of count() for large MySQL tables

The following is my judgment based on the data st...

Vue achieves seamless carousel effect (marquee)

This article example shares the specific code of ...

Use js in html to get the local system time

Copy code The code is as follows: <div id=&quo...