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 delete table data in MySQL

There are two ways to delete data in MySQL, one i...

Analysis of the project process in idea packaging and uploading to cloud service

one. First of all, you have to package it in idea...

Html makes a simple and beautiful login page

Let’s take a look first. HTML source code: XML/HT...

Detailed explanation of Socket (TCP) bind from Linux source code

Table of contents 1. A simplest server-side examp...

HTML+CSS div solution when relative width and absolute width conflict

Div solution when relative width and absolute wid...

Float and Clear Float in Overview Page

1. Float: The main purpose is to achieve the effe...

How to optimize MySQL query speed

In the previous chapters, we introduced how to ch...

Vue code highlighting plug-in comprehensive comparison and evaluation

Table of contents Comprehensive comparison From t...

mysql delete multi-table connection deletion function

Deleting a single table: DELETE FROM tableName WH...

Analysis of the difference between Mysql InnoDB and MyISAM

MySQL supports many types of tables (i.e. storage...

Docker Stack deployment method steps for web cluster

Docker is becoming more and more mature and its f...

Analysis of Sysbench's benchmarking process for MySQL

Preface 1. Benchmarking is a type of performance ...

Detailed steps for deepin20 to install NVIDIA closed-source drivers

Step 1: Install the deep "graphics driver&qu...

An article teaches you how to use js to achieve the barrage effect

Table of contents Create a new html file: Create ...