✅ Query 1:

SELECT
COALESCE(product_name, '') || ', ' ||
COALESCE(product_size, '') || ' (' ||
COALESCE(product_qty_type, 'unit') || ')'
FROM product,

Results:
| COALESCE(product_name, '') || ', ' ||
COALESCE(product_size, '') || ' (' ||
COALESCE(product_qty_type, 'unit') || ')' |
| --- |
| Habanero Peppers - Organic, medium (lbs) |
| Jalapeno Peppers - Organic, small (lbs) |
| Poblano Peppers - Organic, large (unit) |

✅ Query 2:

SELECT
customer_id,
market_date,
DENSE_RANK() OVER (
PARTITION BY customer_id
ORDER BY market_date
) AS visit_number
FROM (
SELECT DISTINCT customer_id, market_date
FROM customer_purchases
)
WHERE market_date < '2022-04-29',

Results:

✅ Query 3:

*SELECT
customer_id,
market_date,
DENSE_RANK() OVER (
PARTITION BY customer_id
ORDER BY market_date DESC
) AS visit_rank
FROM (
SELECT DISTINCT customer_id, market_date
FROM customer_purchases
);

SELECT *
FROM (
SELECT
customer_id,
market_date,
DENSE_RANK() OVER (
PARTITION BY customer_id
ORDER BY market_date DESC
) AS visit_rank
FROM (
SELECT DISTINCT customer_id, market_date
FROM customer_purchases
)
)
WHERE visit_rank = 1*,

Results:

✅ Query 4:

SELECT
customer_id,
product_id,
market_date,
COUNT() OVER (
PARTITION BY customer_id, product_id
) AS purchase_count
FROM customer_purchases
WHERE market_date < '2022-04-29'*,

Results:

✅ Query 5:

SELECT
product_name,
TRIM(
CASE
WHEN INSTR(product_name, '-') > 0 THEN
SUBSTR(product_name, INSTR(product_name, '-') + 1)
ELSE NULL
END
) AS description
FROM product,

Results:

❌ Query 6:

SELECT *
FROM product
WHERE product_size REGEXP '[0-9]',

Error:
no such function: REGEXP

✅ Query 7:

*WITH sales_by_date AS (
SELECT
market_date,
SUM(quantity * cost_to_customer_per_qty) AS total_sales
FROM customer_purchases
GROUP BY market_date
),
ranked AS (
SELECT *,
RANK() OVER (ORDER BY total_sales DESC) AS best_rank,
RANK() OVER (ORDER BY total_sales ASC) AS worst_rank
FROM sales_by_date
)

SELECT market_date, total_sales, 'Best Day' AS label
FROM ranked
WHERE best_rank = 1

UNION

SELECT market_date, total_sales, 'Worst Day' AS label
FROM ranked
WHERE worst_rank = 1*,

Results:

✅ Query 8:

SELECT
v.vendor_name,
p.product_name,
COUNT(c.customer_id) * 5 * vi.original_price AS total_revenue
FROM vendor_inventory vi
JOIN vendor v
ON vi.vendor_id = v.vendor_id
JOIN product p
ON vi.product_id = p.product_id
CROSS JOIN customer c
GROUP BY v.vendor_name, p.product_name, vi.original_price,

Results:

⚪ Query 9:

*CREATE TABLE product_units AS
SELECT ,
CURRENT_TIMESTAMP AS snapshot_timestamp
FROM product
WHERE product_qty_type = 'unit',

No rows returned

⚪ Query 10:

*INSERT INTO product_units
SELECT ,
CURRENT_TIMESTAMP
FROM product
WHERE product_name = 'Apple Pie'
LIMIT 1,

No rows returned

⚪ Query 11:

DELETE FROM product_units
WHERE product_name = 'Apple Pie'
AND snapshot_timestamp < (
SELECT MAX(snapshot_timestamp)
FROM product_units
WHERE product_name = 'Apple Pie'
),

No rows returned

⚪ Query 12:

*ALTER TABLE product_units
ADD current_quantity INT;

UPDATE product_units
SET current_quantity = COALESCE((
SELECT vi.quantity
FROM vendor_inventory vi
WHERE vi.product_id = product_units.product_id
ORDER BY vi.market_date DESC
LIMIT 1
), 0)*,

No rows returned