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));