FAANG SQL INTERVIEW Question | Return IDs who has the highest rating

 DROP TABLE IF EXISTS Friends


CREATE TABLE Friends (

    id INT,

    friend_id INT 

);



DROP TABLE IF EXISTS Ratings


CREATE TABLE Ratings (

    id INT PRIMARY KEY,

    rating INT

);




INSERT INTO Friends (id, friend_id)

VALUES

(1, 2),

(1, 3),

(2, 3),

(3, 4),

(4, 1),

(4, 2),

(5,NULL),

(6,NULL);



INSERT INTO Ratings (id, rating)

VALUES

(1, 85),

(2, 90),

(3, 75),

(4, 88),

(5, 82),

(6, 91)



SELECT * FROM Friends


SELECT * FROM Ratings


-- Retrieve all Ids of a person whose rating is greater than friend's id

-- If person does not have any friend, retrieve only their id only if rating greater than 85

Comments

Post a Comment

Popular posts from this blog

Mu Sigma SQL INTERVIEW QUESTION | Consecutive events count - Dataset 1

 DROP TABLE IF EXISTS [Event]; CREATE TABLE [Event] (     session_id INT,     event_type VARCHAR(50),     event_time DATETIME );  Dataset 1:  -- Insert expanded event data INSERT INTO [Event] (session_id, event_type, event_time) VALUES (1, 'login', '2024-10-01 08:00:00'), (1, 'view', '2024-10-01 08:05:00'), (1, 'purchase', '2024-10-01 08:10:00'), (2, 'login', '2024-10-01 09:00:00'), (2, 'add_to_cart', '2024-10-01 09:05:00'), (2, 'purchase', '2024-10-01 09:10:00'), (3, 'login', '2024-10-01 10:00:00'), (3, 'view', '2024-10-01 10:05:00'), (3, 'purchase', '2024-10-01 10:10:00'), (4, 'login', '2024-10-01 10:30:00'), (4, 'search', '2024-10-01 10:35:00'), (4, 'view', '2024-10-01 10:40:00'), (4, 'add_to_cart', '2024-10-01 10:45:00'), (4, 'purchase', '2024-10-01 10:50:00'), ...
Read more

How to find all the customers who placed orders on three consecutive days | SQL Scenario questions

  DML Script: DROP TABLE IF EXISTS Orders CREATE TABLE Orders ( customer_name VARCHAR(255), order_date DATETIME ); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-01'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-01'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-02'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-02'); INSERT INTO Orders (customer_name, order_date) VALUES ('Charlie', '2024-04-03'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-03'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-04'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-04'); INSERT INTO Orders (customer_name, order_date) VALUES ('Charlie', '2024-04-05'); INSER...
Read more

NTT DATA SQL INTERVIEW QUESTION | Solve 80/20 Pareto rule

 DROP TABLE IF EXISTS Products -- Create the table CREATE TABLE Products (     ProductID INT PRIMARY KEY,     Name VARCHAR(50),     Sales DECIMAL(10, 2) ); -- Insert data into the table INSERT INTO Products (ProductID, Name, Sales) VALUES (1, 'Laptop', 8000.00), (2, 'Smartphone', 7000.00), (3, 'Tablet', 1500.00), (4, 'Headphones', 800.00), (5, 'Smartwatch', 700.00), (6, 'Monitor', 500.00), (7, 'Keyboard', 300.00), (8, 'Mouse', 200.00), (9, 'Charger', 100.00), (10, 'USB Cable', 100.00);  SELECT * FROM Products
Read more