Wakup CSV Queries

GlobalStats.sql

SELECT date_trunc('day', "date") "timestamp", SUM(prints) prints, SUM(opens) opens, SUM(link_opens) link_opens, SUM(new_users) new_users, SUM(unique_users) unique_users
FROM app_hourly_stats
WHERE app_id = 6
GROUP BY "timestamp"
ORDER BY "timestamp" ASC

GlobalWebStats.sql

SELECT date_trunc('day', "date") "timestamp", SUM(web_prints) prints, SUM(web_opens) opens, SUM(web_link_opens) link_opens
FROM app_hourly_stats
WHERE app_id = 6 AND (web_prints <> 0 OR web_opens <> 0)
GROUP BY "timestamp"
ORDER BY "timestamp" ASC

OfferStats.sql

WITH stats AS (
	SELECT offer_id, app_id, SUM(prints) prints, SUM(opens) opens, SUM(link_opens) link_opens,
			SUM(unique_prints) unique_prints, SUM(unique_opens) unique_opens, SUM(unique_link_opens) unique_link_opens
	FROM offer_daily_stats
	WHERE app_id = 6 AND date < '2018-01-01'
	GROUP BY offer_id, app_id
)
SELECT stats.offer_id,
  offer_channels.name channel,
	companies.name company, 
	offer_info.short_description offer,
	stats.prints,
	stats.opens,
	stats.link_opens,
	stats.unique_prints,
	stats.unique_opens,
	stats.unique_link_opens
FROM stats
	INNER JOIN offers ON offers.id = stats.offer_id
	INNER JOIN offer_channels ON offer_channels."id" = offers.channel_id
	INNER JOIN offer_info ON offers."id" = offer_info.offer_id
	INNER JOIN companies ON offers.company_id = companies."id"
ORDER BY prints DESC

OfferStatsByDate.sql

WITH stats AS (
	SELECT offer_id, app_id, SUM(prints) prints, SUM(opens) opens, SUM(link_opens) link_opens,
			SUM(unique_prints) unique_prints, SUM(unique_opens) unique_opens, SUM(unique_link_opens) unique_link_opens
	FROM offer_daily_stats
	WHERE app_id = 6 AND date >= '2017-12-01' AND date < '2018-01-01'
	GROUP BY offer_id, app_id
)
SELECT stats.offer_id,
  offer_channels.name channel,
	companies.name company, 
	offer_info.short_description offer,
	stats.prints,
	stats.opens,
	stats.link_opens,
	stats.unique_prints,
	stats.unique_opens,
	stats.unique_link_opens
FROM stats
	INNER JOIN offers ON offers.id = stats.offer_id
	INNER JOIN offer_channels ON offer_channels."id" = offers.channel_id
	INNER JOIN offer_info ON offers."id" = offer_info.offer_id
	INNER JOIN companies ON offers.company_id = companies."id"
ORDER BY prints DESC

OfferWebStats.sql

WITH stats AS (
	SELECT offer_id, app_id, SUM(web_prints) prints, SUM(web_opens) opens, SUM(web_link_opens) link_opens
	FROM offer_daily_stats
	WHERE app_id = 6 AND date < '2018-01-01'
	GROUP BY offer_id, app_id
)
SELECT stats.offer_id,
  offer_channels.name channel,
	companies.name company, 
	offer_info.short_description offer,
	stats.prints,
	stats.opens,
	stats.link_opens
FROM stats
	INNER JOIN offers ON offers.id = stats.offer_id
	INNER JOIN offer_channels ON offer_channels."id" = offers.channel_id
	INNER JOIN offer_info ON offers."id" = offer_info.offer_id
	INNER JOIN companies ON offers.company_id = companies."id"
ORDER BY prints DESC

OfferWebStatsByDate.sql

WITH stats AS (
	SELECT offer_id, app_id, SUM(web_prints) prints, SUM(web_opens) opens, SUM(web_link_opens) link_opens
	FROM offer_daily_stats
	WHERE app_id = 6 AND date >= '2017-12-01' AND date < '2018-01-01'
	GROUP BY offer_id, app_id
)
SELECT stats.offer_id,
	offer_channels.name channel,
	companies.name company, 
	offer_info.short_description offer,
	stats.prints,
	stats.opens,
	stats.link_opens
FROM stats
	INNER JOIN offers ON offers.id = stats.offer_id
	INNER JOIN offer_channels ON offer_channels."id" = offers.channel_id
	INNER JOIN offer_info ON offers."id" = offer_info.offer_id
	INNER JOIN companies ON offers.company_id = companies."id"
ORDER BY prints DESC