Grocery Store Sales (SQL)

-- Write your query for task 1 in this cell
SELECT COUNT(*) AS missing_year
FROM public.products
--to assess all the missing rows from year_added column
WHERE year_added IS NULL
GROUP BY year_added;	

WITH median_values AS (
    SELECT 
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(REGEXP_REPLACE(weight, '[^0-9.]', '', 'g') AS NUMERIC)) AS median_weight,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_price
    FROM public.products
    WHERE (weight IS NOT NULL AND TRIM(CAST(weight AS TEXT)) != '' AND weight::TEXT NOT IN ('missing', 'na')) 
          OR (price IS NOT NULL AND CAST(price AS TEXT) NOT IN ('missing', 'na'))
)
SELECT 
    product_id,
    -- Handle missing product_type
    CASE 
        WHEN product_type IS NULL THEN 'Unknown'
        WHEN TRIM(product_type) = '' THEN 'Unknown'
        WHEN LOWER(product_type) IN ('-', 'missing', 'na') THEN 'Unknown'
        ELSE product_type 
    END AS product_type,
    
    -- Handle missing or placeholder values for brand
    CASE 
        WHEN brand IS NULL THEN 'Unknown'
        WHEN TRIM(brand) = '' THEN 'Unknown'
        WHEN LOWER(brand) IN ('-', 'missing', 'na') THEN 'Unknown'
        ELSE brand 
    END AS brand,
    
    -- Replace missing weights with the median value and clean numeric weights
    CASE 
        WHEN weight IS NULL THEN 
            (SELECT median_weight FROM median_values)
        WHEN TRIM(CAST(weight AS TEXT)) = '' THEN 
            (SELECT median_weight FROM median_values)
        WHEN CAST(weight AS TEXT) IN ('-','missing', 'na') THEN 
            (SELECT median_weight FROM median_values)
        ELSE 
            CAST(REGEXP_REPLACE(CAST(weight AS TEXT), '[^0-9.]', '', 'g') AS NUMERIC)
    END AS weight,
    
    -- Replace missing prices with the median value
    CASE 
        WHEN price IS NULL THEN 
            (SELECT median_price FROM median_values)
		WHEN TRIM(CAST(price AS TEXT)) = '' THEN 
		    (SELECT median_price FROM median_values)
        WHEN CAST(price AS TEXT) IN ('-','missing', 'na') THEN 
            (SELECT median_price FROM median_values)
        ELSE price
    END AS price,
    
    -- Replace missing average_units_sold with 0
    CASE 
        WHEN average_units_sold IS NULL THEN 0
		WHEN TRIM(CAST(average_units_sold AS TEXT)) = '' THEN 0
		WHEN CAST(average_units_sold AS TEXT) IN ('-','missing', 'na') THEN 0
        ELSE average_units_sold 
    END AS average_units_sold,
    
    -- Replace empty year_added values with '2022'
    CASE 
        WHEN year_added IS NULL THEN '2022'
        WHEN TRIM(CAST(year_added AS TEXT)) = '' THEN '2022'
        WHEN CAST(year_added AS TEXT) IN ('-','missing', 'na') THEN '2022'
        ELSE year_added 
    END AS year_added,
    
    -- Format stock_location to capitalize each word, handle NULL/empty values
    CASE 
        WHEN stock_location IS NULL THEN 'Unknown'
        WHEN TRIM(stock_location) = '' THEN 'Unknown'
        WHEN LOWER(stock_location) IN ('-', 'missing', 'na') THEN 'Unknown'
        ELSE INITCAP(stock_location) 
    END AS stock_location
FROM public.products;

-- Write your query for task 3 in this cell
SELECT product_type,
--maximum & minimum prices for each product_type
MIN(price) AS min_price,
MAX(price) AS max_price
FROM public.products
GROUP BY product_type
--for putting the product_type in alphabetical order
ORDER BY product_type ASC;

-- Write your query for task 4 in this cell
SELECT product_id,
price,
average_units_sold
FROM public.products
--results only for 'Meat' & 'Dairy'
WHERE product_type IN ('Meat', 'Dairy')
GROUP BY product_id, price, average_units_sold
--Avg.units sold greater than 10
HAVING average_units_sold > 10;