SQL Problems Collection

One minute read

Today, I’m gonna write down some SQL problems I’ve solved or faced during interviewing and working.
First Goes First

1. Find the average price of 3 recent days

P/S: This problem is originally from Axon Hackerank Online Test.
You have 1 table named Transactions: Transactions(ID, ResourceID, Price, TransactionTime)

Example data

1insert into transactions(ID, Name, Price, Time)
2values 
3(1, 'Shoes', 100, 20220810),
4(2, 'Hat', 50, 20220809),
5(3, 'Shirt', 50, 20220808);

You are asked to write SQL query showing the average price of 3 recent days for everyday.

Answer:

1select t. time,
2	(
3		select sum(t1.price)/3
4		from transactions t1
5		where t1.time <= t.time AND t1.time >= (t.time - 2)
6
7	) as avg_price
8from transaction_time t;

what is the time complexity of this query?? (nested select) It’s just declarative language, the engine will do its job to optimize the query

Check this email for more detail: https://mail.google.com/mail/u/0/#inbox/FMfcgzGrbRVWLvJWxrLPCdzzGVbdPGKh

3. List out all users with the same phone and email

P/S: This is the result i need to query while doing the job at Geniebook

Answer:

1select u1.id, u1.mobile , u1.email , u2.id, u2.mobile , u2.email 
2from "user" u1 join "user" u2 on u1.mobile =u2.mobile and u1.email=u2.email and u2.id > u1.id;