query_is_returning_result_after_too_long_and, PostgreSQL

Language: Editor: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

drop table if exists Service cascade;

create table Service (user_id int,service_id varchar,created_at timestamp);
INSERT INTO Service
  ( user_id, service_id, created_at )
VALUES
  (201, 'rcygo', '2016-06-22 19:10:25-07'), 
  (32, 'qkbhX', '2016-07-08 06:07:25-07'), 
  (143, 'qmBr2', '2016-03-11 01:11:25-07'),
  (107, 'rcygo', '2017-11-29 21:09:19-07'),
  (32, 'sd4ifdca', '2017-03-22 19:10:25-07'), 
  (143, 'rcygn', '2016-04-08 06:07:25-07'), 
  (144, 'qbArm0', '2016-03-11 01:11:25-07'),
  (32, 'rbEkP', '2016-11-29 21:09:19-07'),
  (201, 'qkbhX', '2016-09-22 19:10:25-07'),
  (201, 'qkbhX', '2016-09-22 19:10:25-07'),
  (107, 'qmBr2', '2017-12-29 21:09:19-07'),
  (201, 'qmBr2', '2017-12-30 21:09:19-07')
;

-- https://www.reddit.com/r/PostgreSQL/comments/cwyaol/query_is_returning_result_after_too_long_and/

WITH get_service_id AS (
	SELECT 'rcygo'::TEXT AS service_id
)
, get_user_service_data AS (
	SELECT 
		  sr.user_id AS user_id
		, COUNT(sr.user_id) AS user_count
		, sr.service_id AS service_id
		, COUNT(sr.service_id) AS service_count
		, 'Total Unique Users of These Other Acquired Services'::TEXT AS dataset
	FROM public.Service AS sr
	GROUP BY sr.user_id, sr.service_id
)
, get_user_by_service_id AS (
	SELECT 
		  user_id
		, user_count
		, service_id
		, service_count
		, 'Unique users Acquiring Entered Service'::TEXT AS dataset
	FROM get_user_service_data
	WHERE service_id = (
		SELECT service_id
		FROM get_service_id
	)
)
, get_services_by_user AS (
	SELECT 
		  user_id
		, user_count
		, service_id
		, service_count
		, 'Other Services Acquired By These Unique Users'::TEXT AS dataset
	FROM get_user_service_data
	WHERE user_id IN (
		SELECT user_id
		FROM get_user_by_service_id
	)
)
, get_all_result_sets AS (
	SELECT 
          user_id
		, user_count
		, service_id
		, service_count
		, dataset
	FROM get_user_by_service_id
	UNION ALL
	SELECT 
          user_id
		, user_count
		, service_id
		, service_count
		, dataset
	FROM get_services_by_user
	UNION -- to pull in unique results, no duplicates
	SELECT 
          user_id
		, user_count
		, service_id
		, service_count
		, dataset
	FROM get_user_service_data
)
, build_json_from_results AS (
    SELECT 
        json_build_object('dataset', dataset, 'user_count', user_count, 'service_id', service_id, 'service_count', service_count) AS metadata
    FROM get_all_result_sets
    ORDER BY dataset DESC
)

SELECT 
      metadata->>'user_count' AS "Unique users Acquiring Entered Service"
    , metadata->>'service_id' AS "Other Services Acquired By These Unique Users"
    , metadata->>'service_count' AS "Total Unique Users of These Other Acquired Services"
FROM build_json_from_results
ORDER BY metadata->>'dataset' DESC


absolute service time: 0,53 sec