Picsart_test

SELECT DISTINCT device_skey 
FROM 'app_opens.csv'
WHERE user_skey='42'

SELECT ROUND(AVG(DISTINCT daily_users), 2) AS avg_daily_users
FROM (
	SELECT date_trunc('day', timestamp) AS date,
		COUNT(DISTINCT device_skey) AS daily_users
	FROM 'app_opens.csv'
	GROUP BY date) AS daily_users_table

SELECT ROUND(AVG(daily_sessions), 2) AS avg_daily_sessions
FROM (
	SELECT date_trunc('day', timestamp) AS date,
	COUNT(DISTINCT session_skey) AS daily_sessions
	FROM 'app_opens.csv'
	GROUP BY date)

SELECT date_trunc('day', timestamp) AS date,
		COUNT(DISTINCT device_skey) AS users,
		platform
FROM 'app_opens.csv'
WHERE is_first_app_open = 'True'
GROUP BY date, platform
ORDER BY date DESC, platform

SELECT platform, ROUND(AVG(users), 2) AS average_daily_new_users
FROM new_users_by_platform
GROUP BY platform

SELECT platform, COUNT(device_skey) AS users_with_multiple_sessions_in_24h
FROM (
	SELECT device_skey, platform, LAG(timestamp) OVER (PARTITION BY device_skey ORDER BY timestamp DESC) - timestamp AS second_time
	FROM app_opens.csv
	WHERE device_skey IN (
		SELECT device_skey
		FROM app_opens.csv	
		WHERE device_skey IN (
			SELECT device_skey
			FROM app_opens.csv
			GROUP BY device_skey
			HAVING COUNT(session_skey) >= 2) AND is_first_app_open = 'True'
		ORDER BY device_skey)
	ORDER BY device_skey, is_first_app_open DESC) AS sub
WHERE second_time < INTERVAL '1 day' 
GROUP BY platform;

SELECT event_name, COUNT(event_name)
FROM events.csv
GROUP BY event_name

SELECT COUNT(*)
FROM events.csv
--WHERE user_skey = 42

SELECT *
FROM app_opens.csv
--WHERE session_skey = '9213749955946524000'

SELECT COUNT(session_skey)
FROM app_opens.csv
WHERE is_first_app_open = 'true'
GROUP BY platform

SELECT DISTINCT event_name, session_skey, COUNT(event_name)
FROM events
WHERE session_skey IN (
	SELECT session_skey
	FROM app_opens.csv
	WHERE is_first_app_open = 'True')
GROUP BY event_name, session_skey

SELECT event_name, e.session_skey, COUNT(event_name), ROW_NUMBER() OVER(PARTITION BY e.session_skey ORDER BY MIN(e.timestamp)) AS event_sequence_number 
FROM events AS e
INNER JOIN app_opens.csv AS a
ON e.session_skey = a.session_skey
WHERE a.is_first_app_open = 'true' AND a.platform = 'apple'
GROUP BY event_name, e.session_skey
ORDER BY e.session_skey



--GROUP BY event_name, e.session_skey
--GROUP BY ROLLUP(event_name, e.session_skey) 
--ORDER BY e.session_skey

SELECT event_sequence_number, event_name, SUM(number_events) AS total_events
FROM (		
	SELECT event_name, e.session_skey, COUNT(event_name) AS number_events, ROW_NUMBER() OVER(PARTITION BY e.session_skey ORDER BY MIN(e.timestamp)) AS event_sequence_number 
	FROM events AS e
	--WHERE session_skey IN (SELECT session_skey FROM app_opens.csv AS a WHERE a.is_first_app_open = 'true' AND a.platform = 'apple')
	INNER JOIN app_opens.csv AS a
	ON e.session_skey = a.session_skey
	WHERE a.is_first_app_open = 'true' AND a.platform = 'apple'
	GROUP BY event_name, e.session_skey
	ORDER BY e.session_skey
) AS sub
GROUP BY CUBE(event_sequence_number, event_name)
ORDER BY event_sequence_number, total_events DESC

SELECT device_skey, timestamp
FROM events
WHERE event_name = 'subscription_done' AND platform = 'apple'

SELECT event_name, COUNT(a.device_skey) AS total_events
FROM app_opens.csv AS a
INNER JOIN events AS e
ON e.session_skey = a.session_skey
WHERE a.is_first_app_open = 'true' AND a.platform = 'apple'
GROUP BY event_name
ORDER BY total_events DESC;