SELECT

DISTINCT b.asset_name AS "Swap From Asset",

c.asset_name AS "Swap To Asset" ,

"Swap From Asset" || ' => ' || "Swap To Asset" AS "Swap Routat"

FROM algorand.defi.fact_swap a

JOIN algorand.core.dim_asset b on a.swap_from_asset_id = b.asset_id

JOIN algorand.core.dim_asset c on a.swap_to_asset_id = c.asset_id

WHERE swap_program = ('{{Program_1}}')

ORDER BY 1

SELECT

CASE

WHEN "Swap From Asset" ilike 'a%' THEN 'A' WHEN "Swap From Asset" ilike 'b%' THEN 'B'

WHEN "Swap From Asset" ilike 'c%' THEN 'C' WHEN "Swap From Asset" ilike 'd%' THEN 'D'

WHEN "Swap From Asset" ilike 'e%' THEN 'E' WHEN "Swap From Asset" ilike 'F%' THEN 'F'

WHEN "Swap From Asset" ilike 'G%' THEN 'G' WHEN "Swap From Asset" ilike 'H%' THEN 'H'

WHEN "Swap From Asset" ilike 'I%' THEN 'I' WHEN "Swap From Asset" ilike 'J%' THEN 'J'

WHEN "Swap From Asset" ilike 'K%' THEN 'K' WHEN "Swap From Asset" ilike 'L%' THEN 'L'

WHEN "Swap From Asset" ilike 'M%' THEN 'M' WHEN "Swap From Asset" ilike 'N%' THEN 'N'

WHEN "Swap From Asset" ilike 'O%' THEN 'O' WHEN "Swap From Asset" ilike 'P%' THEN 'P'

WHEN "Swap From Asset" ilike 'Q%' THEN 'Q' WHEN "Swap From Asset" ilike 'R%' THEN 'R'

WHEN "Swap From Asset" ilike 'S%' THEN 'S' WHEN "Swap From Asset" ilike 'T%' THEN 'T'

WHEN "Swap From Asset" ilike 'U%' THEN 'U' WHEN "Swap From Asset" ilike 'V%' THEN 'V'

WHEN "Swap From Asset" ilike 'W%' THEN 'W' WHEN "Swap From Asset" ilike 'X%' THEN 'X'

WHEN "Swap From Asset" ilike 'Y%' THEN 'Y' WHEN "Swap From Asset" ilike 'Z%' THEN 'Z'

END AS ABC ,

"Swap Routat" , COUNT (*) AS Alphabet_count

FROM base

GROUP BY 1,2