Description

Problem $4$

Write a query based on the vendor$\_$booth$\_$assignments table to display market$\_$date, vendor$\_$id$,$ booth$\_$number, and for each vendor on each market$\_$date, the booth$\_$number from their previous market$\_$date $($name it as previous$\_$booth$\_$number$).$ Sort the results by market$\_$date and then vendor$\_$id$.$

query sql

F$-$ enter your SQL code below:

SELECT

vba.market$\_$date,

vba.vendor$\_$id$,$

vba.booth$\_$number,

COALESCE $($prev$\_$vba.booth$\_$number, $\text{'}$N$/$A$\text{'})$ AS previous$\_$booth$\_$number

FROM

vendor$\_$booth$\_$assignments vba

LEFT JOIN $($

SELECT

vendor$\_$id$,$

market$\_$date,

LAG $($booth$\_$number$)$ OVER $($PARTITION BY vendor$\_$id ORDER BY market$\_$date$)$ AS booth$\_$number

FROM

vendor$\_$booth$\_$assignments

$pre{v}_{v}ba$ ON vba.vendor$\_$id $=$ prev$\_$vba.vendor$\_$id

$7$ AND vba.market$\_$date $=$ prev$\_$vba. market$\_$date

ORDER BY

vba.market$\_$date,

vba.vendor$\_$id;

Description

Problem $5$

Write a query to display market$\_$date, product$\_$name, and product$\_$category$\_$name, as well as a field that is quantity $*$ original$\_$price $($name it as cost$)$ and a field that splits cost into $10$ tiles $($name it as decile$),$ with $10$ being the highest $10\%$ and $1$ being the lowest $10\%.$ Only include products with a product$\_$category$\_$name containing the word 'Fresh' and only during the month of September $2019(\text{'}2019-09-01\text{'}$ to $\text{'}2019-09-30\text{'}).$ Sort the results by market$\_$date and then product$\_$name.

Hint: Join the tables of product$\_$category, product, and vendor$\_$inventory; Use the NTILE$().$

query.sql

$1--$ enter your SQL code below:

SELECT

vi$.$market$\_$date,

p$.$product$\_$name,

pc$.$product$\_$category$\_$name,

vi$.$ quantity $*$ vi$.$original$\_$price AS cost$,$

NTILE$(10)$ OVER $($ORDER BY vi$.$quantity $*$ vi$.$original$\_$price DESC$)$ AS decile

FROM

vendor$\_$inventory vi

JOIN

product $p$ ON vi$.$ product$\_$id $=$ p$.$ product$\_$id

JOIN

product$\_$category pC ON p$.$product$\_$category$\_$id $=$ pc$.$product$\_$category$\_$id

WHERE

pc$.$product$\_$category$\_$name LIKE $\text{'}\%$Fresh$\%\text{'}--$ Filter for product categories containing 'Fresh'

AND vi$.$market$\_$date $\text{'}2019-09-01\text{'}$

AND vi$.$market$\_$date $\text{'}2019-09-30\text{'}--$ Filter for September $2019$

ORDER BY

vi$.$market$\_$date,

p$.$product$\_$name;