Leetcode MEDIUM 1364 - Number of Trusted Contacts MULTIPLE JOINS- Explained by Everyday Data Science
Question: https://leetcode.com/problems/number-...
SQL Schema:
Create table If Not Exists Customers (customer_id int, customer_name varchar(20), email varchar(30))
Create table If Not Exists Contacts (user_id int, contact_name varchar(20), contact_email varchar(30))
Create table If Not Exists Invoices (invoice_id int, price int, user_id int)
Truncate table Customers
insert into Customers (customer_id, customer_name, email) values ('1', 'Alice', '[email protected]')
insert into Customers (customer_id, customer_name, email) values ('2', 'Bob', '[email protected]')
insert into Customers (customer_id, customer_name, email) values ('13', 'John', '[email protected]')
insert into Customers (customer_id, customer_name, email) values ('6', 'Alex', '[email protected]')
Truncate table Contacts
insert into Contacts (user_id, contact_name, contact_email) values ('1', 'Bob', '[email protected]')
insert into Contacts (user_id, contact_name, contact_email) values ('1', 'John', '[email protected]')
insert into Contacts (user_id, contact_name, contact_email) values ('1', 'Jal', '[email protected]')
insert into Contacts (user_id, contact_name, contact_email) values ('2', 'Omar', '[email protected]')
insert into Contacts (user_id, contact_name, contact_email) values ('2', 'Meir', '[email protected]')
insert into Contacts (user_id, contact_name, contact_email) values ('6', 'Alice', '[email protected]')
Truncate table Invoices
insert into Invoices (invoice_id, price, user_id) values ('77', '100', '1')
insert into Invoices (invoice_id, price, user_id) values ('88', '200', '1')
insert into Invoices (invoice_id, price, user_id) values ('99', '300', '2')
insert into Invoices (invoice_id, price, user_id) values ('66', '400', '2')
insert into Invoices (invoice_id, price, user_id) values ('55', '500', '13')
insert into Invoices (invoice_id, price, user_id) values ('44', '60', '6')
Pandas Schema:
data = [[1, 'Alice', '[email protected]'], [2, 'Bob', '[email protected]'], [13, 'John', '[email protected]'], [6, 'Alex', '[email protected]']]
customers = pd.DataFrame(data, columns=['customer_id', 'customer_name', 'email']).astype({'customer_id':'Int64', 'customer_name':'object', 'email':'object'})
data = [[1, 'Bob', '[email protected]'], [1, 'John', '[email protected]'], [1, 'Jal', '[email protected]'], [2, 'Omar', '[email protected]'], [2, 'Meir', '[email protected]'], [6, 'Alice', '[email protected]']]
contacts = pd.DataFrame(data, columns=['user_id', 'contact_name', 'contact_email']).astype({'user_id':'Int64', 'contact_name':'object', 'contact_email':'object'})
data = [[77, 100, 1], [88, 200, 1], [99, 300, 2], [66, 400, 2], [55, 500, 13], [44, 60, 6]]
invoices = pd.DataFrame(data, columns=['invoice_id', 'price', 'user_id']).astype({'invoice_id':'Int64', 'price':'Int64', 'user_id':'Int64'})
#leetcodesolutions #datascience #sql