This is Oracle SQL question that someone asked me in an interview and I was stumped. I am not sure if the question even has a valid answer. So all you SQL gurus out there, please help. Here is the question-
Assume there is a table USER_LOGIN_RECORD with only two columns LOGIN_TIME and USER_ID. This table records the time of login and the username under the appropriate columns whenever a user logs into the database. Say, the table has a thousand rows entered at different times over the day. Can a SQL query be written that will return the number of logins every ten minutes starting from a specified time? It has to be only a query.
It is my assumption that when the interviewer wanted me to give him a “query”, it implies that procedures/functions are not allowed. Writing a function and then using that in a select statement would be as good as calling the function from a script and has not really much to do with formulating a “query”.
When you ask about temporary data structures are allowed, I am not sure about the answer. Even if we assume they are allowed, is it possible to write a single query to give a set of data that needs changing input parameters?
Well, if the LOGIN_TIME can be converted or used as a number, and you can figure out the value for a constant TenMin equalling 10 minutes, and you know how to implement DIV (which I don’t) then it would be something like:
SELECT (LOGIN_TIME DIV TenMin), COUNT(LOGIN_TIME DIV TenMin) FROM USER_LOGIN_RECORD
That would just work for a single interval (like “between 10am and 12pm”). I think wisernow needs a query that can be run at anytime, displaying the results in 10-minute intervals, though. (Is that correct, wisernow?)
Here is a query I put together for SQL Server. I don’t think it can be transferred verbatim to Oracle, but I would be really surprised if Oracle doesn’t have a “translated” version of this. (If nothing else, hopefully it’s a start. :))
select a.interval_num, count(a.interval_num) as login_count
,datediff(n, <<START_DATE>>, LOGIN_TIME) / 10 as interval_num
) as a
group by a.interval_num
Here’s the thinking: The inner query is simply getting a list of the login times, and assigning to each of them a certain number which identifies the “interval” to which the date belongs (i.e., the first 10 minutes, or the second 10 minutes…). After we have that information, we should be able to use the “group by” clause to lump the relevant intervals together.
(The above query uses the T-SQL function “DATEDIFF”, which isn’t available in Oracle. But I’m sure Oracle has its own way of accomplishing the same result.)
I’m not sure what the question is asking for exactly. Does it mean, break up the entire period of time, starting at the input time all the way up until now, into 10-minute intervals and then show the number of hits that occurred during each of those intervals?
IANADBA, so my function names and syntax are probably screwy, but it should be something like
SELECT COUNT(*), SUBSTR(1,14,(TO_CHAR(login_time,'YYYY-MM-DD HH24:MM'))) logt
WHERE login_time > ? GROUP BY logt;
The SUBSTR() function (or whatever it’s called in ORACLE SQL – I’m pretty sure something like it exists) should be parametrized to return all but the last character of the TO_CHAR() rendition of the login_time.
The “?” will of course be replaced by the cutoff date from which you want to start.
You may prefer to replace the “> ?” clause with “between ? and (SELECT sysdate FROM dual)”
Again, not exact code – long time no SQL – but you get the idea.
I am sorry but is dateADD a function available in Oracle? Also, I am not clear what does Date() imply in the query? As far as I can see the query would return only one set of data between start date and the date returned by the dateADD function.
The question was about a query that would return records for every ten minutes.
I do not know T-SQL, but from what I can make out, your inner query seems to be getting the difference between a specified start datetime and the each of the values in the login column of the table, and dividing the difference by 10. I am sorry but I do not understand what is being achieved by this
Is it possible this interview question had a different purpose? Could it be they wanted to see how thoroughly you would inquire about the quality of the input, the expectations of the output, and other considerations like performance? Maybe seeing your approach to solving the problem was the hidden right answer.
So, the first thing to know is that Oracle SQL stores the date as a double-precision floating-point number with a base of one day. The difference of two dates, then, can be multiplied by 144 (24 hours * 6 10-minute intervals) and rounded down (floor) to group by interval. Suppose the date you were given was April 1st, 2004, 8am system time. You could do:
select floor(144 * (login_time - to_date(‘01-04-2004 08:00:00’, ‘dd-mm-yyyy hh24:mi:ss’))) as interval, count(*) as num_logins
where floor(144 * (login_time - to_date(‘01-04-2004 08:00:00’, ‘dd-mm-yyyy hh24:mi:ss’)) > 0
group by floor(144 * (login_time - to_date(‘01-04-2004 08:00:00’, ‘dd-mm-yyyy hh24:mi:ss’)))
I believe that Oracle will perform the actual floor() calculation only once if the strings are identical in all places.
That seems to work Punoqllads. Great coding skills!! But somehow I am not getting the hang of the logic. How does multiplying the 10 minute intervals in 24 hours(144) with the time difference between the start date and login_time give results?
It’s like converting from feet to miles. There are 6 10-minute intervals per hour, and there are 24 hours per day, so converting from days to intervals requires multiplying the difference in days by 144. The floor() function then rounds the value down to the next integer value, e.g., 9 minutes 59 seconds after the initial time will round down to the 0th interval.