Help needed with a Oracle SQL query question.

Factual Questions
1

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.

2

Does this mean you wouldn’t be able to use any pre-defined PL/SQL functions in your query? Or are you just clarifying that no temporary data structures (e.g., tables) can be used?

(I’m assuming the latter…)
LilShieste

3

SELECT COUNT(LOGIN TIME) FROM USER_LOGIN_RECORD WHERE LOGIN_TIME BETWEEN DATE () AND dateAdd(‘m’, - 10, DATE ()))

Or something like that…

4

Something similar to this should work…

DECLARE @StartTime Datetime = ‘BeginTime’
DECLARE @EndTime DateTime = ‘BeginTime + 10 minutes’
DECLARE @Count int = 0

WHILE @Count < 144
BEGIN

SELECT count(*)
FROM User_Login_Record
WHERE Login_Time between (@StartTime, @EndTime)

Set @Count = @Count + 1
Set @StartTime = DateAdd(minute, 10, @StartTime)
Set @EndTime = DateAdd(minute, 10, @StartTime)

END

5

Thanks for trying to help LilShieste.

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?

6

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

7

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
from
    (
        select LOGIN_TIME
              ,datediff(n, <<START_DATE>>, LOGIN_TIME) / 10 as interval_num
        from USER_LOGIN_RECORD
    ) 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.)

Let us know if this helps.
LilShieste

8

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?

9

That was the assumption I was operating under… so I’m curious, too, as to whether or not this is the case.
LilShieste

10

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 
FROM user_login_record 
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.

11

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.

12

That is correct.

13

I am not aware of a datediff function in Oracle.

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

14

I am not sure about Oracle SQL, I was kind of posting while thinking, getting the logic down…

Date() represents the current date and works in MS SQL, but I’m sure that’s one quite the right function…

The ‘m’ I wrote represents the minute… and minus 10.

Your question wasn’t entirely clear. I was thinking that you’d run the query at any point in time to get the number of logins in the previous 10 minutes. Is that not it?

15

edit: “not quite the right function”

16

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.

17

I advise people on this stuff every day. It isn’t that hard and there are multiple solutions. Plain Oracle SQL supports CASE functions as an IF THEN statement for every record.

I would use a CASE statement to chop off the minutes piece of the time and then round it to a specific minute and second like 10.20,30,40, 50, or 60.

From there, you just do a GROUP BY with your username and converted timestamps and a COUNT(USERNAME).

18

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
from user_login_record
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.

19

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?

20

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.