I'm looking for help for one of my previous coding challenge, so I have two table, the first one is Sales and the second one is Product. Which look something like this :
Sales table:
ord_id item_id cust_id status qty total_price order_date
---------------------------------------------------------------------------------
1 A308 CS209 Sold 1 30000 10-10-21
2 A405 CS209 Cancel 4 44000 10-18-21
3 B476 CS890 Sold 8 6400 10-29-21
and Product table
item_id item_category item_price item_name date_created date_updated
----------------------------------------------------------------------------------------
A308 Electronic 30000 TV 08-10-21 10-10-21
A405 Furniture 11000 Chair 12-25-20 10-18-21
B476 Misc 800 Cutter 05-11-21 10-29-21
I was told to write a query to get a result set like this:
order_date detail total_customer total_price
-------------------------------------------------------------------------------------------
10-10-21 {"item_id":"A308","item_category":...} 1 30000
10-29-21 {"item_id":"B476","item_category":...} 1 6400
The other parts are easy but I'm still confused on how to turn certain columns from the Product table into JSON and inserting that json into the Detail column as shown above.
I've done something like this to get the first part :
SELECT order_date, COUNT(cust_id) as total_customer, SUM(total_price) as total_price
FROM Sales
WHERE status = "sold"
GROUP BY order_date
Can anyone help me how to do this in a single query?
