SQLTeam.com | Weblogs | Forums

3 brief sample coding questions

I've been given 3 small sample SQL. While I've been studying SQL (newbie) I am not able to properly code them currently. Might someone please have some coding ideas for these? "Thank you very kindly". See attached 3 small tables
3 SQL Questions
1.) How much commission did sales rep make in the last 3 months?
2.) What is the monthly sales total by territory?
3.)What are the most popular items sold in May?


What have u tried

SELECT COUNT(Order.order_id) as count, op.products_id, op.products_name

FROM orders o

LEFT JOIN orders_products op ON o.orders_id = op.orders_id

WHERE o.date_purchased BETWEEN '2021-05-01 00:00:00' AND '2012-05-31 23:59:59'

GROUP BY op.products_id

this is very good approach for question #3. You are very close .
Remember there cannot be orders for a product that does not exist yet so the sequence of your tables in the query needs to change.

To answer the questions using SQL - you first need to determine what is needed to solve each question.

For question 1 - think about what is needed to determine the commission for each sales rep. Also consider what you need to do to get all sales for the last 3 months (does not include current month?). Once you have identified how to get that information - then you can build a query.

For question 2 - figure out how to identify the territory - and how to identify the monthly sales.

For question 3 - determine how to identify the most popular item(s) and when each item was sold.

Hint: commission is determined by applying the commission rate to the total - and then summing up that value.