gistfile1.txt

SELECT
		'Regi',
		TRUNC(A.createtimestamp) AS dt,
		COUNT(*) AS db_regi,
		COUNT(CASE WHEN C.user_id IS NOT NULL THEN 1 ELSE NULL END) AS segment_regi,
		CAST(100 * COUNT(CASE WHEN C.user_id IS NOT NULL THEN 1 ELSE NULL END)/COUNT(*) AS VARCHAR) + '%' AS perc_missing
	FROM db_userlogininfo A
		INNER JOIN db_user B ON B.userid = A.userid
		LEFT OUTER JOIN vroom.registration C ON C.user_id = B.uniqueid
	WHERE
		TRUNC(A.createtimestamp) >= '2016-05-12' AND
		TRUNC(A.createtimestamp) <= '2016-05-15'
	GROUP BY 2

	UNION ALL

	SELECT
		'Hold',
		TRUNC(starttime),
		COUNT(*),
		COUNT(CASE WHEN C.user_id IS NOT NULL THEN 1 ELSE NULL END),
		CAST(100 * COUNT(CASE WHEN C.user_id IS NOT NULL THEN 1 ELSE NULL END)/COUNT(*) AS VARCHAR) + '%'
	FROM db_stockvehicleonhold A
		INNER JOIN db_deal B ON B.onholdid = A.id
		LEFT OUTER JOIN vroom.hold_submitted C ON C.deal_id = B.dealid
	WHERE
		TRUNC(A.starttime) >= '2016-05-12' AND
		TRUNC(A.starttime) <= '2016-05-15'
	GROUP BY 2

	UNION ALL

	SELECT
		'Deal Created',
		TRUNC(A.createtimestamp),
		COUNT(*),
		COUNT(CASE WHEN B.user_id IS NOT NULL THEN 1 ELSE NULL END),
		CAST(100 * COUNT(CASE WHEN B.user_id IS NOT NULL THEN 1 ELSE NULL END)/COUNT(*) AS VARCHAR) + '%'
	FROM db_deal A
		LEFT OUTER JOIN vroom.deal_created B ON B.deal_id = A.dealid
	WHERE
		TRUNC(A.createtimestamp) >= '2016-05-12' AND
		TRUNC(A.createtimestamp) <= '2016-05-15'
	GROUP BY 2

	UNION ALL

	SELECT
		'Credit App',
		TRUNC(creationdate),
		COUNT(*),
		COUNT(CASE WHEN C.user_id IS NOT NULL THEN 1 ELSE NULL END),
		CAST(ROUND(100 * COUNT(CASE WHEN C.user_id IS NOT NULL THEN 1 ELSE NULL END)/COUNT(*), 1) AS VARCHAR) + '%'
	FROM db_creditapplications A
		INNER JOIN db_deal B ON B.creditapplicationid = A.creditapplicationid
		LEFT OUTER JOIN vroom.credit_app_submitted C ON C.deal_id = B.dealid
	WHERE
		TRUNC(A.creationdate) >= '2016-05-12' AND
		TRUNC(A.creationdate) <= '2016-05-15'
	GROUP BY 2
	ORDER BY 1, 2 DESC