I am writing a query in snowflake to get data from different tables and views and then want to combine the output in one view so others can access it

Question

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

Answer 1

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:

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
1	10	11	5	12	13	14	42	43

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:

PRODUCT_SKU	TOTAL_SALES_TTM	GROSS_MARGIN_TTM
1	10	11

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..