-2

in this table I have to Write a query to fetch the most active user on each day from May 6th, 21 to May 10th, 21. Print user name as null if there are no user sessions on a given date. I have tried everything I could but not able to find any solution , My output basically showed result for one date not for every day , and I searched web I found solutions for Daily active users but I failed to convert this for most daily active user

1

code for creation of table:

DROP TABLE IF EXISTS Sessions;
CREATE TABLE Sessions(
NAME CHAR (20),
CITY CHAR(20),
DATE  datetime,
ACTION CHAR(30)
);
INSERT INTO Sessions
VALUES
('Mahi','Delhi','06/05/21 0:00','Session_Started'),
('Mahi','Delhi','07/05/21 7:00','Session_Started'),
('Mahi','Delhi','07/05/21 8:00','Session_Started'),
('Mahi','Delhi','08/05/21 9:00','Session_Started'),
('Rajesh','Bangalore','06/05/21 9:00','Session_Started'),
('Rajesh','Bangalore','07/05/21 8:00','Session_Started'),
('Rajesh','Bangalore','07/05/21 8:30','Session_Started'),
('Rajesh','Bangalore','08/05/21 60','Session_Started'),
('Sam','Pune','06/05/21 5:00','Session_Started'),
('Sam','Pune','08/05/21 6:00','Session_Started'),
('Sam','Pune','07/05/21 0:00','Session_Started'),
('Sam','Pune','08/05/21 0:00','Session_Started'),
('Sam','Pune','09/05/21 0:00','Session_Started'),
('Vishal','Mumbai','06/05/21 0:00','Session_Started'),
('Vishal','Mumbai','07/05/21 0:00','Session_Started'),
('Vishal','Mumbai','08/05/21 0:00','Session_Started');
Christoph Rackwitz
  • 11,317
  • 4
  • 27
  • 36
RAHUL KIRORIWAL
  • 1
  • 2
  • 1
    by most active, you mean the Session.Name who has max number of row Inserted between dates? – maryam mohammadbagheri Jul 20 '22 at 06:06
  • for each day I have to calculate most active user , so for e.g may 6 th I got two logins from X on 5 pm and 7 pm and one from Y at 4pm , my output should show ria as well as 2 as number of logins , and this has to be for each day – RAHUL KIRORIWAL Jul 20 '22 at 17:51
  • Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) – philipxy Aug 16 '22 at 12:51

1 Answers1

0
select max(active_status),usrname
from user 
where 1-1
and (nvl(date=:P_from_date)) and (sysdate,:P_too_date) 
group by username
Christoph Rackwitz
  • 11,317
  • 4
  • 27
  • 36
SOURABH PATEL
  • 1