Query Language : SQL
Simply saying, here are the tables:
table users (userid, name)
table book (bookid, bookname, author)
table review (reviewId, UserId, BookId, Comment, ReviewDate)
Question: Find users who have reviewed 2 different books on the same day!
Query for creating tables:
create table users (userid int , [name] varchar(50))
create table book (bookid int, bookname varchar(50), author varchar(50))
create table review (reviewId int , UserId int, BookId int, Comment varchar(50), ReviewDate datetime)
Query for data sample:
insert into users values (1, 'A')
insert into users values (2, 'B')
insert into users values (3, 'C')
insert into book values (1, 'Book A', 'Author A')
insert into book values (2, 'Book B', 'Author B')
insert into book values (3, 'Book C', 'Author C')
insert into review values (1, 1, 1, 'AAAA', getdate()-5)
insert into review values (2, 1, 2, 'AAAA', getdate()-5)
insert into review values (3, 1, 3, 'AAAA', getdate()-5)
insert into review values (4, 2, 1, 'AAAA', getdate()-4)
insert into review values (5, 2, 2, 'AAAA', getdate()-4)
insert into review values (6, 2, 1, 'AAAA', getdate())
insert into review values (7, 3, 2, 'AAAA', getdate())
insert into review values (8, 3, 1, 'AAAA', getdate())
By using the query below:
select a.userid, a.userid, b.name, a.bookid, c.bookname, a.reviewdate
from review a
left join users b on b.userid = a.userid
left join book c on c.bookid = a.bookid
The resulting records will be:
Answer:
select * from
(
select userid, bookSum = count(userid), reviewDate = convert(char(10), reviewdate, 105)
from review
group by userid, convert(char(10), reviewdate, 105)
) a
where bookSum = 2
or
select userid, bookSum = count(bookid), reviewDate
from review
group by userid, convert(char(10), reviewdate, 105)
having count(bookid) = 2
And the resulting records will be:
PS: Convert to type 105 is only aimed to convert the type of the date to dd-MM-yyyy so that the date to be compared does not include the time.
