SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Hi experts please help for the below question

4064279Sep 4 2019 — edited Sep 4 2019

can u explain the query to display for each customer for each month????

.a.total login minutes,

b.total bytes downloaded,

c.avg login duration per day,

d.no of days which the user did not login at all

CREATE TABLE T1

(CUST_ID VARCHAR2(30),LOGIN_ID VARCHAR2(30),CUST_NM VARCHAR2(30),CUST_ADDR VARCHAR2(30));

INSERT INTO T1 VALUES

CUST0000000001,,USER1,User_LogiN ID_1,143/1 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34)

INSERT INTO T1 VALUES

(CUST0000000002,USER2,User_LogiN ID_2,143/2 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34)

INSERT INTO T1 VALUES

(CUST0000000003,USER3,User_LogiN ID_3,143/3 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34);

INSERT INTO T1 VALUES

(CUST0000000004,USER4,User_LogiN ID_4,143/4 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34);

COMMIT;

CREATE TABLE T2

(CONNECTION_ID VARCHAR2(30),

LOGIN_ID VARCHAR2(30),

LOGIN_DTTM VARCHAR2(30),LOGOUT_DTTM VARCHAR2(30),BYTES_CNT VARCHAR2(30),

DAILY_BILLING_STATUS VARCHAR2(30),MONTHLY_BILLING_STATUS VARCHAR2(30),

LOGIN_SESSION_BILLING_AMT VARCHAR2(30));

INSERT INTO T2 VALUES('CON0000000001','USER1','01-SEP-08','01-SEP-08',100,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000002','USER1','02-SEP-08','01-SEP-08',500,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000003','USER1','1-SEP-08,'10-SEP-08',1500,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000004','USER1','3-SEP-08,'30-SEP-08',10,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000005','USER1','3-SEP-08,'01-OCT-08',100,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000006','USER1','0-OCT-08,'05-OCT-08',12000,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000007','USER1','1-SEP-08,'17-SEP-08',1000,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000008','USER1','24-SEP-08','24-SEP-08',1500,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000009','USER2','01-SEP-08','01-SEP-08',100,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000010','USER2','01-SEP-08','01-SEP-08',500,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000011','USER2','10-SEP-08','10-SEP-08',1500,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000012','USER2','30-SEP-08','30-SEP-08',10,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000013','USER2','30-SEP-08','01-OCT-08',100,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000014','USER2','03-OCT-08','05-OCT-08',12000,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000015','USER2','16-SEP-08','17-SEP-08',1000,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000016','USER2','24-SEP-08','24-SEP-08',1500,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000017','USER3','01-SEP-08','01-SEP-08',100,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000018','USER3','01-SEP-08','01-SEP-08',500,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000019','USER3','10-SEP-08','10-SEP-08',1500,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000020','USER3','30-SEP-08','30-SEP-08',10,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000021','USER3','30-SEP-08','01-OCT-08',100,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000022','USER3','03-OCT-08','05-OCT-08',12000,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000023','USER3','16-SEP-08','17-SEP-08',1000,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000024','USER3','24-SEP-08','24-SEP-08',1500,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000025','USER4','01-SEP-08','01-SEP-08',100,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000026','USER4','01-SEP-08','01-SEP-08',500,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000027','USER4','10-SEP-08','10-SEP-08',1500,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000028','USER4','30-SEP-08','30-SEP-08',10,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000029','USER4','30-SEP-08','01-OCT-08',100,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000030','USER4','03-OCT-08','05-OCT-08',12000,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000031','USER4','16-SEP-08','17-SEP-08',1000,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000032','USER4','24-SEP-08','24-SEP-08',1500,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000033','USER1','24-SEP-07','24-SEP-07',122,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000034','USER1','25-SEP-07','26-SEP-07',544,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000035','USER2','25-SEP-07','26-SEP-07',355,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000036','USER3','24-SEP-07','24-SEP-07',100,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000037','USER3','25-SEP-07','26-SEP-07',10,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000038','USER3','25-DEC-07','25-DEC-07',1000,'N',N,NULL );

Added on Sep 4 2019

#sql-developer

10 comments

292 views