CODEWITHSUNDEEP
sqlpostgresqlwith-statement
Anton Griduhsko
Anton Griduhsko

Reputation: 101

How to escape several with in the query?

I need to refactor my query, but I have no idea, how to do this.

I can see several duplicates of using the same logic, but I continue to repeat this manner of querying from query to query, and I feel, that this kind of querying becomes my main frame of thinking of SQL and I don't want this.

Could you show me more acceptable variant of this query, so I won't repeat my way of thinking again?

Here it is

WITH fourth_table AS
(
    WITH third_table AS
    (
        WITH second_table AS
        (
            WITH initial_table AS
            (
                SELECT 
                DISTINCT ctr.country_region, EXTRACT (YEAR FROM s.time_id)::int AS calendar_year, chn.channel_desc, 
                SUM(s.amount_sold) OVER (PARTITION BY ctr.country_region||chn.channel_desc||EXTRACT (YEAR FROM s.time_id)) AS amount_sold
                FROM sales s
                JOIN channels chn ON s.channel_id = chn.channel_id
                JOIN customers c ON s.cust_id  = c.cust_id
                JOIN countries ctr ON c.country_id = ctr.country_id
                WHERE ctr.country_region IN ('Americas','Asia', 'Europe')
                AND
                EXTRACT (YEAR FROM s.time_id)::int IN (1998, 1999, 2000, 2001)
                ORDER BY ctr.country_region, calendar_year, chn.channel_desc
            )
                SELECT country_region, calendar_year, channel_desc, amount_sold,
                (amount_sold/SUM(amount_sold) OVER (PARTITION BY country_region||calendar_year)*100)::decimal(10,2) AS bychannels
                FROM initial_table
        )
        SELECT *,
        LAG (bychannels, 4) OVER (ORDER BY 6) AS lower_salary
        FROM second_table--correct here smth wrong
    )
    SELECT *, bychannels - lower_salary AS diff FROM third_table
)
SELECT country_region, calendar_year, channel_desc, 
--'FM     999,999,999,990D'
LPAD(to_char(amount_sold, 'FM999,999,999,990 $'),20, ' ') AS amount_sold,
LPAD(bychannels || ' %' ,20, ' ') AS "% BY CHANNELS",
LPAD(lower_salary || ' %    ' ,20, ' ') AS "% PREVIOUS PERIOD", 
diff AS "% DIFF"
FROM fourth_table WHERE calendar_year NOT IN (1998);

Upvotes: 0

Views: 43

Answers (1)

Philipp Johannis
Philipp Johannis

Reputation: 2956

You are mixing CTE (Common Table Queries) with Subqueries, the beauty of the with clause is normally the readability:

with initial_table as
(
    SELECT 
    DISTINCT ctr.country_region, EXTRACT (YEAR FROM s.time_id)::int AS calendar_year, chn.channel_desc, 
    SUM(s.amount_sold) OVER (PARTITION BY ctr.country_region||chn.channel_desc||EXTRACT (YEAR FROM s.time_id)) AS amount_sold
    FROM sales s
    JOIN channels chn ON s.channel_id = chn.channel_id
    JOIN customers c ON s.cust_id  = c.cust_id
    JOIN countries ctr ON c.country_id = ctr.country_id
    WHERE ctr.country_region IN ('Americas','Asia', 'Europe')
    AND
    EXTRACT (YEAR FROM s.time_id)::int IN (1998, 1999, 2000, 2001)
    ORDER BY ctr.country_region, calendar_year, chn.channel_desc
)
,second_table as
(
    SELECT country_region, calendar_year, channel_desc, amount_sold,
    (amount_sold/SUM(amount_sold) OVER (PARTITION BY country_region||calendar_year)*100)::decimal(10,2) AS bychannels
    FROM initial_table
)
,third_table as
(
    SELECT *,
    LAG (bychannels, 4) OVER (ORDER BY 6) AS lower_salary
    FROM second_table--correct here smth wrong
)
,fourth_table as
(
    SELECT *, bychannels - lower_salary AS diff FROM third_table
)
SELECT country_region, calendar_year, channel_desc, 
--'FM     999,999,999,990D'
LPAD(to_char(amount_sold, 'FM999,999,999,990 $'),20, ' ') AS amount_sold,
LPAD(bychannels || ' %' ,20, ' ') AS "% BY CHANNELS",
LPAD(lower_salary || ' %    ' ,20, ' ') AS "% PREVIOUS PERIOD", 
diff AS "% DIFF"
FROM fourth_table WHERE calendar_year NOT IN (1998);

Upvotes: 2

Related Questions