Tool used

• Postgresql

How I solved

• Created a database and a table named "moloco_test".
• Inserted all the data from the spreadsheet into the table.
• Used Queries below for getting the answers

Queries

Q1.

SELECT site_id, COUNT(DISTINCT user_id) as unique_users 
FROM moloco_test 
WHERE country_id='BDV' 
GROUP BY site_id;

Q2.

SELECT user_id, site_id, COUNT(user_id) 
FROM moloco_test 
WHERE ts >= '2019-02-03 00:00:00' AND ts <= '2019-02-04 23:59:59' 
GROUP BY user_id, site_id 
HAVING (COUNT(user_id)) > 10;

Q3.

SELECT site_id, COUNT(site_id) 
FROM 
	(SELECT user_id, MAX(ts) 
	FROM moloco_test 
	GROUP BY user_id) AS latest_info 
INNER JOIN moloco_test ON moloco_test.ts = latest_info.max AND moloco_test.user_id = latest_info.user_id
GROUP BY site_id
ORDER BY count DESC;

Q4.

SELECT COUNT(first_visits.user_id)
FROM 
	(SELECT moloco_test.user_id, site_id as first_site, ts 
	FROM 
		(SELECT user_id, MIN(ts) as first_visit 
		FROM moloco_test 
		GROUP BY user_id) as first_visits 
	INNER JOIN moloco_test 
	ON moloco_test.user_id = first_visits.user_id AND ts = first_visit) as first_visits 
INNER JOIN 
	(SELECT moloco_test.user_id, site_id as last_site, ts 
	FROM 
		(SELECT user_id, MAX(ts) as last_visit 
		FROM moloco_test 
		GROUP BY user_id) as last_visits 
	INNER JOIN moloco_test 
	ON moloco_test.user_id = last_visits.user_id AND ts = last_visit) as last_visits 
ON first_visits.user_id = last_visits.user_id
WHERE first_site=last_site;