Ndtyjky

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:

1. 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?


2. Is there a way that I can improve my code so that it can still run in situation like this?

Thank you!

try this

New versions of MySQL WorkBench have an option to change specific timeouts.

For me it was under Edit → Preferences → SQL Editor → DBMS connection read time out (in seconds): 600

Reference : here