query

query

(
	SELECT
		SUM(smartbites_created_count) AS smartbites_created,
		SUM(smartbites_consumed_count) AS smartbites_consumed,
		AVG(user_metrics_aggregations.time_spent_minutes) AS average_session,
		COUNT(DISTINCT(teams_users.user_id)) AS total_users,
		teams.id AS team_id,
		COUNT(DISTINCT(teams_users.user_id)) AS active_users,
		COUNT(DISTINCT(teams_users.user_id)) AS new_users
	FROM user_metrics_aggregations
	INNER JOIN users ON users.id = user_metrics_aggregations.user_id
	INNER JOIN teams_users ON teams_users.user_id = users.id
	INNER JOIN teams ON teams.id = teams_users.team_id
	WHERE users.organization_id = 15
	AND user_metrics_aggregations.time_spent_minutes > 0
	GROUP BY team_id
)
UNION ALL
(
	SELECT
		SUM(smartbites_created_count) AS smartbites_created,
		SUM(smartbites_consumed_count) AS smartbites_consumed,
		AVG(user_metrics_aggregations.time_spent_minutes) AS average_session,
		COUNT(DISTINCT(teams_users.user_id)) AS total_users,
		teams.id AS team_id,
	   '' AS active_users,
		COUNT(DISTINCT(teams_users.user_id)) AS new_users
	FROM user_metrics_aggregations
	INNER JOIN users ON users.id = user_metrics_aggregations.user_id
	INNER JOIN teams_users ON teams_users.user_id = users.id
	INNER JOIN teams ON teams.id = teams_users.team_id
	WHERE users.organization_id = 15
	AND users.created_at > '2018/09/01'
	AND users.created_at < '2018/09/01'
	GROUP BY team_id
)
UNION ALL
(
	SELECT
		SUM(smartbites_created_count) AS smartbites_created,
		SUM(smartbites_consumed_count) AS smartbites_consumed,
		AVG(user_metrics_aggregations.time_spent_minutes) AS average_session,
		COUNT(DISTINCT(teams_users.user_id)) AS total_users,
		teams.id AS team_id,
		'' AS active_users,
		'' AS new_users
	FROM user_metrics_aggregations
	INNER JOIN users ON users.id = user_metrics_aggregations.user_id
	INNER JOIN teams_users ON teams_users.user_id = users.id
	INNER JOIN teams ON teams.id = teams_users.team_id
	WHERE users.organization_id = 15
	GROUP BY team_id
)
ORDER BY new_users DESC
LIMIT 20