-- Aisha Lund — Cross-Grain Distribution

-- Question 1
SELECT beers.beerName, beers.abv, beers.ibu FROM craftbrewery.beers WHERE beers.isActive = 1;

-- Question 2
SELECT beers.beerName, styles.styleName FROM craftbrewery.beers JOIN craftbrewery.styles ON beers.styleId = styles.styleId ORDER BY styles.styleName ASC, beers.beerName ASC;

-- Question 3
SELECT accountName, city, COUNT(orderId) AS totalOrders FROM accounts LEFT JOIN orders ON accountId = accountId GROUP BY accountId, accountName, city ORDER BY totalOrders DESC;

-- Question 4
SELECT beers.beerName, COUNT(DISTINCT DATE(taproomSales.saleDate)) AS distinctSaleDates FROM craftbrewery.beers JOIN craftbrewery.taproomSales ON beers.beerId = taproomSales.beerId GROUP BY beers.beerId, beers.beerName HAVING COUNT(DISTINCT DATE(taproomSales.saleDate)) > 3 ORDER BY distinctSaleDates DESC;

-- Question 5
SELECT territories.territoryName, territories.salesRep, SUM(orderLines.lineTotal) AS totalRevenue FROM craftbrewery.territories JOIN craftbrewery.accounts ON territories.territoryId = accounts.territoryId JOIN craftbrewery.orders ON accounts.accountId = orders.accountId JOIN craftbrewery.orderLines ON orders.orderId = orderLines.orderId WHERE orders.status = 'Delivered' GROUP BY territories.territoryId, territories.territoryName, territories.salesRep ORDER BY totalRevenue DESC;

-- Question 6
SELECT batches.batchCode, batches.brewDate, batches.volumeLitres FROM craftbrewery.batches WHERE batches.beerId IN (SELECT beerTags.beerId FROM craftbrewery.beerTags WHERE beerTags.tagName = 'Seasonal') ORDER BY batches.brewDate ASC;

-- Question 7
-- (not attempted)

-- Question 8
SELECT DISTINCT accounts.accountName, accounts.accountType, accounts.city FROM craftbrewery.accounts JOIN craftbrewery.orders ON accounts.accountId = orders.accountId JOIN craftbrewery.orderLines ON orders.orderId = orderLines.orderId WHERE orderLines.beerId IN (SELECT orderLines.beerId FROM craftbrewery.orderLines GROUP BY orderLines.beerId HAVING SUM(orderLines.quantity) > 500) ORDER BY accounts.accountName ASC;
