I am using below query to achieve two measures total code used and total pairs done but the query is very slow. Can someone please suggest on how to do it in an optimized manner.
SELECT
code AS p_code,
name AS p_name,
(CASE
WHEN b.line IN ('Excel', 'Excel (Inject Fi)') THEN 'Excel'
WHEN b.line IN ('Excel XL', 'Excel XL (Inject Fi)')
THEN 'Excel XL'
WHEN b.line IN ('Excel 2', 'Excel 2 (Inject Fi)') THEN 'Excel 2'
WHEN b.line IN ('Excel 2 XL', 'Excel 2 XL (Inject Fi)')
THEN 'Excel 2 XL'
WHEN b.line IN ('Excel 3', 'Excel 3 (Inject Fi)') THEN 'Excel 3'
WHEN b.line IN ('Excel 3 XL', 'Excel 3 XL (Inject Fi)')
THEN 'Excel 3 XL'
WHEN b.line IN ('Excel 3a', 'Excel 3a (Inject Fi)')
THEN 'Excel 3a'
WHEN b.line IN ('Excel 3a XL', 'Excel 3a XL (Inject Fi)')
THEN 'Excel 3a XL'
ELSE b.line END) AS p_line,
COUNT(code) AS total_code_used
FROM usage a
LEFT JOIN
details b
ON
a.number = b.id
AND a.check = b.ast
AND (CASE WHEN a.center = 'YN' THEN 'Yawn' ELSE a.center END)
= b.center
WHERE
a.deport_date IS NOT NULL
AND b.id IS NOT NULL
AND b.received_date IS NOT NULL
AND b.line IS NOT NULL
AND b.line <> ""
GROUP by 1, 2, 3) a
LEFT JOIN
(SELECT
COUNT(DISTINCT CONCAT(number,im)) AS total_pair,
(CASE
WHEN b.line IN ('Excel', 'Excel (Inject Fi)') THEN 'Excel'
WHEN b.line IN ('Excel XL', 'Excel XL (Inject Fi)')
THEN 'Excel XL'
WHEN b.line IN ('Excel 2', 'Excel 2 (Inject Fi)') THEN 'Excel 2'
WHEN b.line IN ('Excel 2 XL', 'Excel 2 XL (Inject Fi)')
THEN 'Excel 2 XL'
WHEN b.line IN ('Excel 3', 'Excel 3 (Inject Fi)') THEN 'Excel 3'
WHEN b.line IN ('Excel 3 XL', 'Excel 3 XL (Inject Fi)')
THEN 'Excel 3 XL'
WHEN b.line IN ('Excel 3a', 'Excel 3a (Inject Fi)')
THEN 'Excel 3a'
WHEN b.line IN ('Excel 3a XL', 'Excel 3a XL (Inject Fi)')
THEN 'Excel 3a XL'
ELSE b.line END) AS p_line
FROM usage a
LEFT JOIN
details b
ON
a.number = b.id
AND a.check = b.ast
AND (CASE WHEN a.center = 'YN' THEN 'Yawn' ELSE a.center END)
= b.center
WHERE
a.deport_date IS NOT NULL
AND b.id IS NOT NULL
AND b.received_date IS NOT NULL
AND b.line IS NOT NULL
AND b.line <> ""
GROUP BY 2) b
ON a.pline = b.pline
RIGHT JOIN
(SELECT
line_unnest,
gpn,
category
FROM
look_up_table,
UNNEST(SPLIT(line,'/')) AS line_unnest) c
ON
c.gpn = a.pcode
AND (CASE WHEN c.line_unnest IN ('Excel 3aXL') THEN 'Excel 3a XL'
ELSE c.line_unnest END)
=a.pline
WHERE
p_code IS NOT NULL