Question: leetcode.com/problems/reporte...
SQL Schema:
Create table If Not Exists Actions (user_id int, post_id int, action_date date, action ENUM('view', 'like', 'reaction', 'comment', 'report', 'share'), extra varchar(10))
create table if not exists Removals (post_id int, remove_date date)
Truncate table Actions
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'view', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'like', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'share', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('2', '2', '2019-07-04', 'view', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('2', '2', '2019-07-04', 'report', 'spam')
insert into Actions (user_id, post_id, action_date, action, extra) values ('3', '4', '2019-07-04', 'view', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('3', '4', '2019-07-04', 'report', 'spam')
insert into Actions (user_id, post_id, action_date, action, extra) values ('4', '3', '2019-07-02', 'view', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('4', '3', '2019-07-02', 'report', 'spam')
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '2', '2019-07-03', 'view', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '2', '2019-07-03', 'report', 'racism')
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '5', '2019-07-03', 'view', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '5', '2019-07-03', 'report', 'racism')
Truncate table Removals
insert into Removals (post_id, remove_date) values ('2', '2019-07-20')
insert into Removals (post_id, remove_date) values ('3', '2019-07-18')
Pandas Schema:
data = [[1, 1, '2019-07-01', 'view', None], [1, 1, '2019-07-01', 'like', None], [1, 1, '2019-07-01', 'share', None], [2, 2, '2019-07-04', 'view', None], [2, 2, '2019-07-04', 'report', 'spam'], [3, 4, '2019-07-04', 'view', None], [3, 4, '2019-07-04', 'report', 'spam'], [4, 3, '2019-07-02', 'view', None], [4, 3, '2019-07-02', 'report', 'spam'], [5, 2, '2019-07-03', 'view', None], [5, 2, '2019-07-03', 'report', 'racism'], [5, 5, '2019-07-03', 'view', None], [5, 5, '2019-07-03', 'report', 'racism']]
actions = pd.DataFrame(data, columns=['user_id', 'post_id', 'action_date', 'action', 'extra']).astype({'user_id':'Int64', 'post_id':'Int64', 'action_date':'datetime64[ns]', 'action':'object', 'extra':'object'})
data = [[2, '2019-07-20'], [3, '2019-07-18']]
removals = pd.DataFrame(data, columns=['post_id', 'remove_date']).astype({'post_id':'Int64', 'remove_date':'datetime64[ns]'})
#datascience #mysqltutorials #leetcodesolutions