SQL questions

Q1

https://www.interviewquery.com/questions/comments-histogram

CREATE TABLE TESTS.USERS (ID INTEGER PRIMARY KEY NOT NULL, NAME VARCHAR(100), CREATED_AT DATE, NEIGHBORHOOD_ID INTEGER, MAIL VARCHAR(100));

CREATE TABLE TESTS.COMMENTS(USER_ID INTEGER, BODY VARCHAR(16000), CREATED_AT DATE );

INSERT INTO TESTS.USERS VALUES(1,'John',NULL,NULL,NULL);
INSERT INTO TESTS.USERS VALUES(2,'Bob',NULL,NULL,NULL);

INSERT INTO TESTS.COMMENTS VALUES(1,NULL,'2020-01-10');
INSERT INTO TESTS.COMMENTS VALUES(2,NULL,'2020-01-02');
INSERT INTO TESTS.COMMENTS VALUES(2,NULL,'2020-01-01');
INSERT INTO TESTS.COMMENTS VALUES(2,NULL,'2020-02-01');

Solution

SELECT ID, COUNT(USER_ID) FROM TESTS.USERS,TESTS.COMMENTS AS C WHERE ID = C.USER_ID AND C.CREATED_AT BETWEEN '2020-01-01'AND '2020-01-31' GROUP BY ID

Q2

https://www.interviewquery.com/questions/post-success

CREATE TABLE TESTS.EVENTS (ID INTEGER, USER_ID INTEGER, CREATED_AT DATE, ACTION VARCHAR(100),URL VARCHAR(1000), PLATFORM VARCHAR(100));

INSERT INTO TESTS.EVENTS VALUES(1,NULL,'2020-01-02','post_enter',NULL,NULL);
INSERT INTO TESTS.EVENTS VALUES(1,NULL,'2020-01-02','post_submit',NULL,NULL);

INSERT INTO TESTS.EVENTS VALUES(2,NULL,'2020-01-02','post_enter',NULL,NULL);
INSERT INTO TESTS.EVENTS VALUES(2,NULL,'2020-01-02','post_cancel',NULL,NULL);

INSERT INTO TESTS.EVENTS VALUES(3,NULL,'2020-01-03','post_submit',NULL,NULL);
INSERT INTO TESTS.EVENTS VALUES(3,NULL,'2020-01-03','post_enter',NULL,NULL);

INSERT INTO TESTS.EVENTS VALUES(4,NULL,'2020-01-03','post_submit',NULL,NULL),(4,NULL,'2020-01-03','post_enter',NULL,NULL);

Solution 1 (complicated). Identity ID successful (post_enter->post_commit) using INTERSECT and join with all entered.

SELECT A.DAY,SUCCESS * 100.0 /FLOAT(ALL) FROM 
(SELECT DAY(CREATED_AT) AS DAY,COUNT(ID) AS SUCCESS FROM (
SELECT ID,CREATED_AT FROM TESTS.EVENTS WHERE CREATED_AT BETWEEN '2020-01-01' AND '2020-01-31' AND ACTION = 'post_enter'
INTERSECT 
SELECT ID,CREATED_AT FROM TESTS.EVENTS WHERE CREATED_AT BETWEEN '2020-01-01' AND '2020-01-31' AND ACTION = 'post_submit' )
GROUP BY DAY(CREATED_AT)) AS S,
(SELECT DAY(CREATED_AT) AS DAY,COUNT(ID) AS ALL FROM TESTS.EVENTS WHERE CREATED_AT BETWEEN '2020-01-01' AND '2020-01-31' AND ACTION = 'post_enter' GROUP BY DAY(CREATED_AT)) AS A
WHERE S.DAY = A.DAY

Solution2 (simple), simply divide number of 'post_submit' by 'post_enter'

SELECT DAY(CREATED_AT) AS DAY,SUM(CASE WHEN ACTION='post_submit' THEN 1 ELSE 0 END) * 100 / SUM(CASE WHEN ACTION='post_enter' THEN 1 ELSE 0 END)  AS ALL FROM TESTS.EVENTS WHERE CREATED_AT BETWEEN '2020-01-01' AND '2020-01-31' GROUP BY DAY(CREATED_AT)