SELECT p.product_id, ROUND(COALESCE(SUM(price * units) / NULLIF(SUM(units), 0), 0), 2) AS average_price FROM Prices p JOIN UnitsSold u ON u.product_id = p.product_id AND u.purchase_date BETWEEN p.start_date AND p.end_date GROUP BY p.product_id; Only passes 15/17 test cases, not sure why Tried…
I'm writing a simple program to find what cocktails somebody is able to make given only the ingredients that they already have on hand. The schema looks something like this: Table CocktailIngredients cocktailId ingredientId 0 0 0 2 1 0…
Given: 2 tables: sales and purchases Sales: product_id, count, date Purchases: product_id, purchase number, count of items in the purchase, cost per unit Task: To calculate the cost of goods sold using the FIFO (first in - first out, first…
Hello everyone) I use Laravel, MariaDB. For example, we have a table like this, let's say categories. id name _lft _rgt parent_id path is_folder 1373 Windows 1 10 NULL 1373 1 1374 Glass unit 2 7 1373 1373.1374 1 1375…
I was solving some SQL tasks and got confused with the following thing: CREATE TABLE data ( len INT ); INSERT INTO data (len) VALUES (NULL), (100), (200), (300), (400); suppose we've created such a table and we wanna count…
please look at the image , you will find mysql CASE WHEN executed even condition is false _skus sku_id is_available is_quantity quantity 13 1 0 NUll UPDATE _skus SET is_available = CASE WHEN is_quantity = 1 AND quantity=0 THEN 0…
I'm trying to update a table in MySQL 8.0.35, but it seems like it doesn't like the way I'm formatting this query. I can insert into tables this way, but I get an error when I try to use it…
I have two tables, master and Details, and I need to generate a report that counts the occurrences of each status value for each remark. Here's the structure of my tables: Table: master m_id (Primary Key) remarks (TEXT) Table: Details…
I'm working on a monitoring system based on MySQL. The system consists of a table which records checks (e.g. a crawled website) for multiple users. The users shall be notified about their last failed (NOK) check but only once a…
I have a query writing problem. My table: -> table users_keyword_document document_id | users_keyword_id -------------+------------------ 65 | 1 65 | 2 66 | 1 66 | 3 I want to retrieve only the document with document_id 65 The search can…