I am pretty new to SQL and I recently had a SQL test. In the test, I was given a laptop and was asked to solve some SQL problems in MySQL. The data for testing is a dataset which records when each user logs into a website and it looks like this (the dataset I was given was much larger than this though):
CREATE TABLE table1 (id INT, login TIMESTAMP);
INSERT INTO table1 (id, login) VALUES
(1,'2018-11-01 00:00:01'),
(1,'2018-11-02 11:00:01'),
(1,'2018-11-03 13:00:01'),
(1,'2018-11-04 15:00:01'),
(1,'2018-11-05 17:00:01'),
(2,'2018-10-01 00:00:01'),
(2,'2018-10-11 10:00:01'),
(2,'2018-10-17 09:00:01'),
(2,'2018-11-11 08:00:01'),
(3,'2018-09-03 14:00:01'),
(3,'2018-09-04 15:00:01'),
(3,'2018-09-04 16:00:01'),
(3,'2018-09-06 18:00:01'),
(3,'2018-09-06 19:00:01'),
(3,'2018-09-06 20:00:01'),
(3,'2018-09-13 04:00:01'),
(3,'2018-09-13 14:00:01'),
(3,'2018-09-23 14:00:01'),
(4,'2018-10-03 11:00:01'),
(4,'2018-11-03 12:00:01'),
(5,'2018-09-01 08:00:01'),
(5,'2018-09-02 09:00:01'),
(5,'2018-09-12 09:00:01'),
(5,'2018-09-22 10:00:01'),
(5,'2018-09-22 19:00:01'),
(6,'2018-10-15 06:00:01'),
(6,'2018-10-18 09:00:01'),
(6,'2018-10-18 10:00:01'),
(6,'2018-10-18 11:00:01'),
(6,'2018-10-19 12:00:01');
And I need to figure out the difference in terms of no. of days between a user's first time visit and each of his/her subsequent visits. The result table should look like:
id, date of first time visit, current visit, difference between first time and current
And I wrote the following codes:
SELECT t1.id, t1.first_time, t2.login AS cur_time, DATEDIFF(t2.login, t1.first_time) AS diff
FROM
(SELECT id, min(login) as first_time
FROM table1
GROUP BY id) t1 JOIN table1 t2
ON t1.id=t2.id;
When I tried to run the code using the laptop provided, I got error message saying 'lost connection during query'.
But I didn't have issues running other code like:
SELECT id, min(login) as first_time
FROM table1
GROUP BY id;
However, I got the same error message even for some simple code like this:
SELECT *
FROM
(SELECT id, min(login) as first_time
FROM table1
GROUP BY id) t1;
or when I tried to create view or create temporary table.
Also, when I tried the code on my own laptop, I didnt get the error message.
My question is:
- Why did I get the error message when I was using the testing laptop? I tried Google the error message and I guess that's because my query takes too long to run and it exceeds the time limit that the MySQL version of that laptop allows. I guess whoever sets up the test only wants some efficient code?
- Is there a way that I can improve my code so that it can still run in situation like this?
Thank you!
