Optimization of duplicates code block in query

Transact-SQL
1

Hello
I would like to clean out duplicates here
Could you show the better way to rewrite my query?

--DROP FUNCTION get_customers_rental_activity;

CREATE OR REPLACE FUNCTION get_customers_rental_activity (IN i_client_id INTEGER, i_left_boundary DATE, i_right_boundary DATE) 
RETURNS TABLE (metric_name# TEXT, metric_value#  TEXT) 
	LANGUAGE plpgsql
AS $$
BEGIN
RETURN query 
SELECT 'customer''s info#' AS "name", first_name || ', ' || last_name || ', ' || email AS "value" 
FROM customer c
WHERE customer_id = i_client_id
UNION ALL
SELECT 'num.of films rented#' AS "name", COUNT(i.film_id)::VARCHAR(5)  AS "value"
	FROM payment p
	JOIN rental r ON p.rental_id = r.rental_id
	JOIN inventory i ON r.inventory_id = i.inventory_id
	WHERE r.rental_date >= (i_left_boundary::DATE)
	AND r.rental_date <= (i_right_boundary::DATE)
	AND p.customer_id = i_client_id
	UNION ALL
SELECT 'rented film''s titles#' AS "name", array_to_string(array_agg(DISTINCT f.title), ', ') AS "value" 
	FROM payment p
	JOIN rental r ON p.rental_id = r.rental_id
	JOIN inventory i ON r.inventory_id = i.inventory_id
	JOIN film f ON i.film_id = f.film_id
	WHERE r.rental_date >= (i_left_boundary::DATE)
	AND r.rental_date <= (i_right_boundary::DATE)
	AND p.customer_id = i_client_id
	GROUP BY p.customer_id
	UNION ALL
SELECT 'num.of payments#' AS "name", count(p.payment_id)::VARCHAR(5) AS "value" 
	FROM payment p
	JOIN rental r ON p.rental_id = r.rental_id
	JOIN inventory i ON r.inventory_id = i.inventory_id
	WHERE r.rental_date >= (i_left_boundary::DATE)
	AND r.rental_date <= (i_right_boundary::DATE)
	AND p.customer_id = i_client_id
	UNION ALL
SELECT 'payment''s amount#' AS "name", sum(p.amount)::varchar(5) AS "value"
	FROM payment p
	JOIN rental r ON p.rental_id = r.rental_id
	JOIN inventory i ON r.inventory_id = i.inventory_id
	WHERE r.rental_date >= (i_left_boundary::DATE)
	AND r.rental_date <= (i_right_boundary::DATE)
	AND p.customer_id = i_client_id;
end;$$

--check query

SELECT * FROM get_customers_rental_activity(60, '2005-05-03', '2017-05-03');

2

one idea ( among many others )

please see it and modify it

SELECT 
   'customer''s info#' AS "name", first_name || ', ' || last_name || ', ' || email AS "value" 
FROM 
  customer c
WHERE 
    customer_id = i_client_id


UNION ALL

SELECT 
   'num.of films rented#' AS "name", COUNT(i.film_id)::VARCHAR(5)  AS "value"
   ,'rented film''s titles#' AS "name", array_to_string(array_agg(DISTINCT f.title), ', ') AS "value" 
   ,'num.of payments#' AS "name", count(p.payment_id)::VARCHAR(5) AS "value" 
   ,'payment''s amount#' AS "name", sum(p.amount)::varchar(5) AS "value"
FROM 
	( select 	payment p
		JOIN rental r ON p.rental_id = r.rental_id
		JOIN inventory i ON r.inventory_id = i.inventory_id
		WHERE r.rental_date >= (i_left_boundary::DATE)
		AND r.rental_date <= (i_right_boundary::DATE)
		AND p.customer_id = i_client_id ) 
GROUP BY 
   p.customer_id
1 Like
3

Thank You! I will try it !!