by Session Variables!
Did you even know that you can use variables in your SQL queries outwith stored procedures? No? Well, I’m here to tell you you can.
Set Up Code
CREATE TABLE w78 AS
SELECT
SEQ4() AS sales_id,
CASE
WHEN MOD(SEQ4(), 4) = 0 THEN 'Product A'
WHEN MOD(SEQ4(), 4) = 1 THEN 'Product B'
WHEN MOD(SEQ4(), 4) = 2 THEN 'Product C'
ELSE 'Product D'
END AS product_name,
UNIFORM(1, 10, RANDOM())::INTEGER AS quantity_sold,
DATEADD('day', -UNIFORM(1, 365, RANDOM())::INTEGER, CURRENT_DATE()) AS sales_date,
UNIFORM(20, 100, RANDOM())::FLOAT * UNIFORM(1, 10, RANDOM())::INTEGER AS sales_amount
FROM TABLE(GENERATOR(ROWCOUNT => 1000));Run the above code, and then create a variable called “sales_avg”.
Run the below script:
SELECT *
FROM w78
WHERE sales_amount between $sales_avg - 50 and $sales_avg +50;Your result should look like the below (numbers will vary)
A quick challenge to end the week, thanks!
One line solutions!
https://github.com/marioveld/frosty_friday/tree/main/ffw78
Easier than I expected, although it took me 2-3 trials and errors to get to the correct SQL statements, especially in terms of placing parentheses.