CREATE TEMPORARY TABLE sales AS
SELECT
    PRODUCT_SKU,
    sum(GROSS_SALES - GROSS_RETURNS - COUPON_DISCOUNT) total_sales_ttm,
    sum(GROSS_MARGIN) as gross_margin_ttm,
    avg(COST_OF_GOODS_SOLD - COST_OF_RETURNS) as avg_cost_ttm,
    sum(QUANTITY_SOLD - QUANTITY_RETURNED) as unit_sold_ttm
FROM TABLE1 
WHERE
TRAN_DATE BETWEEN (CURRENT_DATE -365) AND CURRENT_DATE
GROUP BY 1;

CREATE TEMPORARY TABLE q_sales AS
SELECT 
    PRODUCT_SKU,
    sum(GROSS_SALES_AMOUNT - GROSS_RETURNS_AMOUNT) q_total_sales_ttm,
    sum(GROSS_MARGIN) as q_gross_margin_ttm,
    avg(COST_OF_GOODS_SOLD - COST_OF_RETURNS) as q_avg_cost_ttm,
    sum(QUANTITY_SOLD - QUANTITY_RETURNED) as q_unit_sold_ttm
FROM TABLE2 
WHERE
TRAN_DATE BETWEEN (CURRENT_DATE -365) AND CURRENT_DATE
GROUP BY 1;

CREATE TEMPORARY TABLE prices AS
SELECT 
    rp.SKU, 
    rp.PRICE AS RETAIL_PRICE,
    bp.PRICE AS BASELINE_PRICE
FROM TABLE3 rp
LEFT JOIN TABLE4 bp 
ON rp.SKU=bp.SKU
WHERE
rp.history=1 AND bp.history=1;

//Combining temp tables into one query

SELECT
    sales.PRODUCT_SKU, 
    total_sales_ttm, gross_margin_ttm, avg_cost_ttm,
    q_total_sales_ttm, q_gross_margin_ttm, q_avg_cost_ttm,
    retail_price,baseline_price
FROM
sales
LEFT JOIN q_sales
ON sales.PRODUCT_SKU = q_sales.PRODUCT_SKU
LEFT JOIN prices
ON sales.PRODUCT_SKU=prices.SKU

CodePudding user response：

You might want to use transient tables instead of Temporary Tables, as Temporary tables are scoped within a session.

You can then combine these views/tables into a single view and have a Role with SELECT access to the final view, so that users with the role have read-only access to the view.

If you could clarify your ask here further, can help better.

CodePudding user response：

So if I make some test data tables:

CREATE TABLE sales AS
SELECT
    1 as PRODUCT_SKU,
    10 as total_sales_ttm,
    11 as gross_margin_ttm,
    5 as avg_cost_ttm,
    4 as unit_sold_ttm
;

CREATE TABLE q_sales AS
SELECT 
    1 as PRODUCT_SKU,
    12 as q_total_sales_ttm,
    13 as q_gross_margin_ttm,
    14 as q_avg_cost_ttm,
    15 as q_unit_sold_ttm
;

CREATE TEMPORARY TABLE prices AS
SELECT 
    1 as SKU, 
    42 AS RETAIL_PRICE,
    43 AS BASELINE_PRICE

your select:

SELECT
    sales.PRODUCT_SKU, 
    total_sales_ttm, gross_margin_ttm, avg_cost_ttm,
    q_total_sales_ttm, q_gross_margin_ttm, q_avg_cost_ttm,
    retail_price,baseline_price
FROM
sales
LEFT JOIN q_sales
ON sales.PRODUCT_SKU = q_sales.PRODUCT_SKU
LEFT JOIN prices
ON sales.PRODUCT_SKU=prices.SKU;

gives:

thus to make a view:

CREATE VIEW view_name as 
SELECT
    sales.PRODUCT_SKU, 
    total_sales_ttm, gross_margin_ttm, avg_cost_ttm,
    q_total_sales_ttm, q_gross_margin_ttm, q_avg_cost_ttm,
    retail_price,baseline_price
FROM
sales
LEFT JOIN q_sales
ON sales.PRODUCT_SKU = q_sales.PRODUCT_SKU
LEFT JOIN prices
ON sales.PRODUCT_SKU=prices.SKU;

thus:

select PRODUCT_SKU, TOTAL_SALES_TTM, GROSS_MARGIN_TTM from view_name;

gives:

You will need to make sure the other users have permission to the database/schema where the view is, and give the permissions to run the view, and thus the tables also..