CODEWITHSUNDEEP
sql-servert-sql
Dvintila
Dvintila

Reputation: 212

TSQL Group by Order by

The following is an actual interview question that I actually failed. Just curious what is the correct answer.

Given the following tables:

SELECT * FROM users;

user_id  username
1        John Doe                                                                                            
2        Jane Don                                                                                            
3        Alice Jones                                                                                         
4        Lisa Romero

SELECT * FROM training_details;

user_training_id  user_id  training_id  training_date
1                 1        1            "2015-08-02"
2                 2        1            "2015-08-03"
3                 3        2            "2015-08-02"
4                 4        2            "2015-08-04"
5                 2        2            "2015-08-03"
6                 1        1            "2015-08-02"
7                 3        2            "2015-08-04"
8                 4        3            "2015-08-03"
9                 1        4            "2015-08-03"
10                3        1            "2015-08-02"
11                4        2            "2015-08-04"
12                3        2            "2015-08-02"
13                1        1            "2015-08-02"
14                4        3            "2015-08-03"

Write a query to to get the list of users who took the a training lesson more than once in the same day, grouped by user and training lesson, each ordered from the most recent lesson date to oldest date.

Upvotes: 0

Views: 4938

Answers (5)

Nishar Khan
Nishar Khan

Reputation: 1

Select username, training_Date from users, training _details where users.user_id=training_details.user_id group by username having count(Training _id)>1 order by training_Date desc;

Upvotes: 0

Sushant Nimoria
Sushant Nimoria

Reputation: 1

SELECT username, training_date ,  count(*) as count
FROM users u
INNER JOIN training_details t ON(u.user_id = t.user_id)
GROUP BY username, training_date
having count(*) > 1
ORDER BY username,training_date DESC

Upvotes: 0

Ajinkya
Ajinkya

Reputation: 11

Select
   u.user_id,
   username,
   training_id,
   training_date,
   count (user_training_id) AS count
From users u JOIN training_details t ON t.user_id= u.user_id
Group By user_id, 
         username, 
         training_id,
         training_date
Having count(user_training_ id) > 1
Order By training_date DESC;

Upvotes: 1

Neeraj Shukla
Neeraj Shukla

Reputation: 11

`SELECT u.USERNAME, t.USER_ID, t.TRAINING_DATE, COUNT(t.TRAINING_ID) 
 FROM training_details t JOIN USERS u ON (u.id = t.user_id)
 GROUP BY u.USERNAME,t.USER_ID, t.TRAINING_ID, t.TRAINING_DATE
 HAVING COUNT(t.TRAINING_ID)>1
 ORDER BY t.TRAINING_DATE DESC;`


Results => 

"USERNAME",     "USER_ID","TRAINING_DATE","COUNT(T.TRAINING_ID)"
"Lisa Romero",   4,         08-04-2015,        2
"Lisa Romero",   4,         08-03-2015,        2
"John Doe",      1,         08-02-2015,        3
"Alice Jones",   3,         08-02-2015,        2

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

Unless I'm missing something, it should be something like this:

SELECT username, tranning_date
FROM users u
INNER JOIN training_details t ON(u.user_id = t.user_id)
GROUP BY username, tranning_date
HAVING COUNT(*) > 1
ORDER BY username, tranning_date DESC

Upvotes: 4

Related Questions