Hello, I have to answer the following question to have a interview for a new job. Could you verify if my answer is correct ? I am not sure because of the timestamp data type and the CURRENT_TIMESTAMP for the variable 'login_time'. Thank you very much.

Using the table definition below and assuming such table exists with 750 million records, write an efficient SQL query (or queries) that returns the total number of logins and the number of unique profiles that logged in between January 1, 2015 and February 15, 2015, inclusively.  Explain your logic.

CREATE TABLE `LoginLog` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `profile_id` int(11) DEFAULT '-1',

  `mailer_id` int(10) unsigned DEFAULT '0',

  `entry_point` enum('UNKNOWN','MAIN_SITE','REGISTRATION','MAILER','MOBILE') NOT NULL DEFAULT 'UNKNOWN',

  PRIMARY KEY (`id`),

  KEY `profile_id_idx` (`profile_id`))

  ENGINE=MyISAM AUTO_INCREMENT=459520272 DEFAULT CHARSET=utf8

);

My answer are :

1-  Script for total number of unique profiles that logged in between January 1, 2015 and February 15, 2015, inclusively

Select count (distinct profile_id)

From LoginLog

Where login_time is  >=  2015-01-01 00 00 01 and login_time is  <= 2015-02-15 23 59 59;

2-Script for returns the total number of logins between January 1, 2015 and February 15, 2015, inclusively.

-- Hypothesis : 1 line = 1 login--

Select count (Id)

From LoginLog

Where login_time is  >=  2015-01-01 00 00 01 and login_time is  <= 2015-02-15 23 59 59;

One thing is that you are missing logins done between 2015-01-01 00 00 00.000 and 2015-01-01 00 00 00.997. And also between 2015-02-15 23 59 59.003 and 2015-02-15 23 59 59.997.

No, wait. We cannot answer your question since it apparently isn't an SQL Server question. The data type is apparently timestamp, and the timestamp datatype has nothing to do with date and time in SQL Server. So, we would need to know what DBMS they have in mind and also the details for the timestamp data type in that DBMS. In genera, though, we prefer below type of searches for date time ranges:

Where login_time is  >=  2015-01-01 00:00:00 and login_time is  < 2015-02-16 00:00:00;

Above logic should also work for other DBMSs and it is SARGable.

As Tibor said, this is not a definition of the SQL Server table. Do you know which Database we're talking about here?

Thank you very much. I tought I was stupid or not able to find the right info.

Maybe that's the trick, I can not answer the question because the data type is wrong in the CREATE TABLE.

As Tibor said, this is not a definition of the SQL Server table. Do you know which Database we're talking about here?

Isn't the pleothora of grave accents a token of MySQL?

I know they also worked with MySQL but I never use this language. Maybe this is what they wanted. Thank you very much.

I was guessing it's MySQL, but I never worked with it, so I didn't know for sure.

I confirm the langage is MySQL. I post the same question on another Forum. Some users told me it is MySQL.