SQL Interview Questions_part 2

4. Social Network

[Problem]

A new social network site has the following data tables:

 

USERSIDNAMESEX

1	Ann	null
2	Steve	m
3	Mary	f
4	Brenda	f

FRIENDSUSER1USER2

1	2
1	3
2	3

 

Select data that will be returned by the following SQL query:

(Select all acceptable answers.)

 

[My Answer]

Marry, 2 and Branda,1 

---

5. Sessions

[Problem]

App usage data are kept in the following table:

Write a query that selects userId and average session duration for each user who has more than one session.

 

[My Answer]

select 
userId, avg(duration)
from sessions
group by userId
having count(userId) >1

Write a query that selects the item name and the name of its seller for each item that belongs to a seller with a rating greater than 4. The query should return the name of the item as the first column and name of the seller as the second column.

[My Answer]

select 
B.name as items_name, A.name as seller_name
from sellers A 
inner join
items B 
on A.id=B.sellerId
where A.rating >4;