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)

Q3

https://www.interviewquery.com/questions/liked-pages

CREATE TABLE FRIENDS (USER_ID INTEGER, FRIEND_ID INTEGER);
CREATE TABLE PAGE_LIKES(USER_ID INTEGER,PAGE_ID INTEGER);

INSERT INTO FRIENDS VALUES (1,10),(1,11);

INSERT INTO PAGE_LIKES VALUES(10,1000),(10,1001),(11,1001),(11,1002);

INSERT INTO FRIENDS VALUES (2,10),(2,12),(2,13);
INSERT INTO PAGE_LIKES VALUES(12,1000),(12,1003),(13,1000);

Select all pages and the number of likes attached.

SELECT F.USER_ID,PAGE_ID,COUNT(PAGE_ID) AS COUNT FROM FRIENDS AS F,PAGE_LIKES AS P WHERE F.FRIEND_ID = P.USER_ID GROUP BY F.USER_ID,PAGE_ID ORDER BY USER_ID,COUNT(PAGE_ID) DESC

Select using CTE and join.

WITH P(USER_ID,PAGE_ID,C) AS 
(SELECT F.USER_ID,PAGE_ID,COUNT(PAGE_ID) AS COUNT FROM FRIENDS AS F,PAGE_LIKES AS P WHERE F.FRIEND_ID = P.USER_ID GROUP BY F.USER_ID,PAGE_ID ORDER BY USER_ID,COUNT(PAGE_ID))
SELECT USER_ID,PAGE_ID,C FROM P WHERE C = (SELECT MAX(C) FROM P AS PP WHERE P.USER_ID = PP.USER_ID)

Q4

https://www.interviewquery.com/questions/average-order-value

CREATE TABLE TRANSACTIONS (ID INT, USER_ID INT, CREATED_AT DATE, PRODUCT_ID INT, QUANTITY INT);
CREATE TABLE USERS (ID INT, NAME VARCHAR(100), SEX CHAR(1));
CREATE TABLE PRODUCTS (ID INT, NAME VARCHAR(100), PRICE FLOAT);

INSERT INTO USERS VALUES(100, 'Jane','F');
INSERT INTO USERS VALUES(101, 'Bob','M');

INSERT INTO TRANSACTIONS VALUES(1,100,NULL,1000,2);
INSERT INTO TRANSACTIONS VALUES(1,100,NULL,1001,5);
INSERT INTO TRANSACTIONS VALUES(2,100,NULL,1000,1);

INSERT INTO TRANSACTIONS VALUES(2,101,NULL,1000,1);
INSERT INTO TRANSACTIONS VALUES(3,101,NULL,1001,1);

INSERT INTO PRODUCTS VALUES(1000,'Mobile',200.23);
INSERT INTO PRODUCTS VALUES(1001,'Battery',0.5);

Solution

SELECT SEX, ROUND(AVG(VALUE),2) FROM (
SELECT SEX, T.ID, SUM(T.QUANTITY * P.PRICE) AS VALUE FROM TRANSACTIONS T ,USERS U ,PRODUCTS P WHERE U.ID = T.USER_ID AND T.PRODUCT_ID = P.ID GROUP BY SEX,T.ID
) GROUP BY SEX

Q4

The table contains the numbers. Subtract the greatest number from the second greatest number.

create table numbers (X INT);

insert into numbers values(1),(2),(3),(10),(7);

Solution. Create a CTE table containing only the greatest (LT), another CTE table with the second greatest number (PT), join and subtract.

with PT as (select * from (select X,row_number() over (order by x desc) as N from numbers) as T where n = 2),
LT as (select * from (select X,row_number() over (order by x desc) as N from numbers) as T where n = 1)
select LT.X - PT.X from LT,PT;

Q5

https://app.codility.com/programmers/trainings/6/sql_events_delta/

 create table events (
      event_type integer not null,
      value integer not null,
      time timestamp not null,
      unique(event_type, time)
  );

insert into events values( 2, 5,' 2015-05-09 12:42:00'),( 4 , -42, '2015-05-09 13:19:57'),( 2, 2,' 2015-05-09 14:48:30'),(2,7,'2015-05-09 12:54:39'),(3, 16,' 2015-05-09 13:19:57'),( 3, 20,' 2015-05-09 15:01:09');

Solution. Create LT table containing event_type and the latest timestamp, ST containing the second latest timestamp, join by event_type and subtract.

with LT as (
select event_type,value, N from (select event_type,value,time,row_number() over (partition by event_type order by time desc) as N from events) as GT where N = 1),
ST as (select event_type,value, N from (select event_type,value,time,row_number() over (partition by event_type order by time desc) as N from events) as GT where N = 2)
select LT.event_type,LT.value - ST.value from LT,ST where LT.event_type = ST.event_type;