I have table which has a column of type json with name "demographic". I want to compare keys(predefined by me) by their values and the result of that should be the name of the key and not the value which later i will use to save to another column's rows. The json columns that i have and want to compare values looks like this:

{
   "he":{
      "he_vakiy":29384,
      "he_naiset":14803,
      "he_miehet":14581,
      "he_kika":1485,
      "he_0_2":850,
      "he_3_6"
   }
}

I want to compare f.ex "he_naiset" and "he_miehet" and the one that has highest value should return the name of the "key" ; Ive tried something like this: select greatest(demographics->'he'->>'he_miehet',demographics->'he'->>'he_naiset') as greatest from demographics; Which will compare the values and return the highest values. But I don't know how I can get the name of the key. I also tried something like this from: with cte as ( select * , genre.key as genre , row_number() over (partition by id order by value desc) as ord from base.maakunta cross join lateral json_each_text(base.maakunta.demographic->'he') genre ) select genre, value,ord from cte where ord = 1 ; But this one compares all the keys inside the JSON.

I'm looking to build a question answer survey system where some questions will be based on the answer of the parent question. The hierarchy level of the questions can go any number of depth based on the questions. The questions and answers will be like the diagram shown here. I'm looking to build this in mongodb. I have checked the tree model in mongodb and I'm clueless on how to structure it as the next set of questions are depends on the answers. The questions should show based on what answer the user have selected and some answers may have multiple question sets below that as well (E.g.: Ans B). Can someone help me on the mongodb collection structure and how can I query the next set of questions and answers based on the previous answer given by the user? Thanks in advance.

I have a development server that has some problem to access the data, user reporting that it's too much slow sometimes. The setup is: * virtual server; * 4 virtual CPU; * 8 GB of virtual memory ; * 80 GB of virtual HD (the real HD is a SDD one), I had still 36 GB available; * OS Debian 9; * Mysql 5.6.47; To avoid all problems about network and the Web App, I simply do my queries directly on the host where Mysql is installed. I had enabled the logging of slow query, and find the slowest query. This query start from a certain table, that I report below:

CREATE TABLE MALICIOUS_TABLE (
  column_1 int(11) NOT NULL AUTO_INCREMENT,
  column_2 varchar(8) NOT NULL,
  column_3 datetime NOT NULL,
  column_4 int(11) NOT NULL,
  column_5 int(11) DEFAULT NULL,
  column_6 int(11) DEFAULT NULL,
  column_7 int(11) DEFAULT NULL,
  column_8 tinyint(1) DEFAULT NULL,
  column_9 datetime DEFAULT NULL,
  column_10 int(11) DEFAULT NULL,
  column_11 varchar(2048) DEFAULT 'column_11',
  column_12 tinyint(1) DEFAULT NULL,
  column_13 datetime DEFAULT NULL,
  column_14 tinyint(1) DEFAULT NULL,
  PRIMARY KEY (column_1),
  KEY fk_ual_aut_idx (column_2),
  KEY fk_aul_c_idx (column_4),
  KEY kf_ual_po_idx (column_5),
  KEY fk_ual_ute_idx (column_10),
  KEY column_1 (column_1),
  KEY column_2 (column_2),
  CONSTRAINT fk_aul_c FOREIGN KEY (column_4) REFERENCES t_table2 (column_4) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_ual_aut FOREIGN KEY (column_2) REFERENCES t_tabl3 (column_2) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_ual_po FOREIGN KEY (column_5) REFERENCES t_table4 (column_5) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_ual_ute FOREIGN KEY (column_10) REFERENCES t_table5 (column_10) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2357917 DEFAULT CHARSET=latin1

The table has a not so small number of records:

select count(*) from MALICIOUS_TABLE;
+----------+
| count(*) |
+----------+
|  2308414 |
+----------+
1 row in set (2,67 sec)

If I try the slowest query, always from the mysql command line on the server, every about 10 seconds, I got different response times, this is the production server, so users keep insert data:

SELECT count(*) FROM MALICIOUS_TABLE WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666411 |
+----------+
1 row in set (4,39 sec)

SELECT count(*) FROM MALICIOUS_TABLE WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666477 |
+----------+
1 row in set (4,94 sec)

SELECT count(*) FROM MALICIOUS_TABLE WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666752 |
+----------+
1 row in set (17,02 sec)

The last attempt has a great variation of response time. At the beginning I thought that maybe indexes are the problem, I drop them and recreate them. Yet I got the huge variation of the response time. The RAM of the server it's good, still getting about 2 giga of free RAM. The query caching of Mysql it's active, and maybe the second attempt retrieve the query from the cache, and the last one no. Any suggestion of what I can check to understand the problem? The machine, the db (now I'm trying to modify query cache settings) or the table itself? Thank you in advance.

I am trying to create a report from a cloud based EHR so I cannot share real data and some of these tables are fairly massive. I will try to minimize and share the bare minimum and expand if someone needs more information to help. This should be fairly easy and I'm just having a brain fart I think. I need to combine multiple answers into a single row as separate columns. Here is my query as it is and it does return all the answers but every answer is generating a separate row. There will only ever be one answer for each question per visit id. There are a few catches to working with this system. At it's heart it's SQLServer, however queries are restricted to starting with 'select' making temp tables a bit more difficult. There can be no spaces, no blank lines nothing before your select. This is their version of security I guess. All reports are written through a web interface no direct access to the db in any way. Current Output: **

clientvisit_id  |  client_id  |  members_present  |  patient_category  

**

141001          |  2001       |                   |
141001          |  2001       |                   |  	 
141001          |  2001       |  Patient          |  	 
141001          |  2001       |                   |  Adult  	 

Desired output: **

clientvisit_id  |  client_id  |  members_present  |  patient_category  

**

141001          |  2001       |  Patient          |  Adult 	 

Select cv.clientvisit_id, cv.client_id, mp.answer as members_present, pc.answer as patient_category From ClientVisit cv Inner Join SavedVisitAnswer sva On sva.clientvisit_id = cv.clientvisit_id Inner Join Question q On sva.question_id = q.question_id Inner Join Category cat On q.category_id = cat.category_id Inner Join FormVersion fv On cat.form_ver_id = fv.form_ver_id Inner Join Forms On fv.form_id = Forms.form_id Inner Join (Select a1.answer_id, a1.answer From Answer a1 Where a1.question_id = '532096' ) as pc on sva.answer_id = pc.answer_id Inner Join (Select a2.answer, a2.answer_id From Answer a2 Where a2.question_id = '532093' ) as mp on sva.answer_id = mp.answer_id Where Forms.form_id = '246'

I am using MySQL in trying to solve a problem relating to a SQL query classifying types of nodes in a binary tree . Specifically, the question states as follows: > You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N. Both columns have datatype as integer. Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node: > > - Root: If node is root node. > - Leaf: If node is leaf node. > - Inner: If node is neither root nor leaf node. Here is my attempt so far:

SELECT N,  
CASE  
    WHEN bst_outer.P IS NULL THEN "Root"  
    WHEN NOT EXISTS(SELECT bst_inner.N FROM BST bst_inner WHERE bst_inner.P = bst_outer.P) THEN "Leaf"  
    ELSE 'Inner'  
END   
FROM BST bst_outer;

The output after running the above query gives incorrect result, as there appears to be no nodes with Leaf classification. It seems like that the error lies on my second WHEN statement, but I am not sure how to fix it.

This is probably easier to explain with a toy example. I have a MSSQL database with two tables. Firstly, I have table A, which has two columns: "Dish_name" and "Dish_ingredients". It looks something like this: | Dish_name | Dish_ingredients| | -------- | ------------ | | Strawberry cheesecake | Butter, wheat, cream cheese, sugar, blueberry jam | | Beef stew | Beef, tomatoes, carrots, celery, canola oil, onions | | Spaghetti carbonara | Pasta, eggs, heavy cream, pork belly, salt | | Avocado toast | Toast, avocado | | ... | ... | The second table, Table B, lists all the ingredients I may find and whetehr they are dairy products, have gluten, etc. |Ingredient|Is_dairy|Has_gluten| |----------|--------|----------| |Butter|1|0| |Wheat|0|1| |Cream cheese|1|0| |Sugar|0|0| |Blueberry jam|0|0| |Beef|0|0| |Tomatoes|0|0| |Carrots|0|0| |Celery|0|0| |Canola oil|0|0| |Onions|0|0| |Pasta|0|1| |Eggs|0|0| |Heavy cream|1|0| |Pork belly|0|0| |Salt|0|0| |Toast|0|1| |Avocado|0|0| | ... | ... | ... | Based on these two tables, I'm trying to make a table that tells me if my dishes are ok for lactose intolerant people so I can make a menu for my party. I throw a similar party every month and since I add more dishes and ingredients to my tables all the time, the query must be able to keep up without me going back and changing its structure every time. Case-sensitivity does not bother me, but I wouldn't rely on always having commas as separators in "Dish_ingredients".

I have a table with 256 lottery drawings, pick 5, along with the drawing date. **I want to know how many times my favorite numbers (4,7,47) or any numbers, came out at the same drawing.** I am thinking

select * from lotto where
ball1 = 4, or ball2 = 4, or ball3 = 4, or ball4 = 4, or ball5 = 4
and
ball1 = 7, or ball2 = 7, or ball3 = 7, or ball4 = 7, or ball5 = 7
and
ball1 = 47, or ball2 = 47, or ball3 = 47, or ball4 = 47, or ball5 = 47

But I am not sure if this is the correct approach. I also want to do this with any 3 numbers, or hell, find which 3 numbers have appeared together, from all combinations of # of total balls. Thanks for your time. EDIT: I have found the solution for any 3 given numbers.

SELECT lotto.ball1, lotto.ball2, lotto.ball3, lotto.ball4, lotto.ball5
FROM lotto
WHERE
(
((lotto.ball1)=4) OR ((lotto.ball2)=4) OR ((lotto.ball3)=4) OR ((lotto.ball4)=4) OR ((lotto.ball5)=4)
)
AND
(
((lotto.ball1)=7) OR ((lotto.ball2)=7) OR ((lotto.ball3)=7) OR ((lotto.ball4)=7) OR ((lotto.ball5)=7)
)
AND
(
((lotto.ball1)=47) OR ((lotto.ball2)=47) OR ((lotto.ball3)=47) OR ((lotto.ball4)=47) OR ((lotto.ball5)=47)
)
;

Now, I wonder how to analyze the database to show ALL drawings where ANY permutation of 3 numbers came out together. Off to google I suppose. I will update this question if I find an answer -- or I will "answer" my own question.

I have java app that is using MySQL in the backend. I have the following table: A = int, B = varchar, C = timestamp A | B | C 1 | 100 | 2022-03-01 12:00:00 2 | 200 | 2022-03-01 12:00:01 3 | 100 | 2022-03-01 12:00:01 4 | 200 | 2022-03-01 12:00:02 5 | 600 | 2022-03-01 12:00:03 1 | 100 | 2022-03-01 12:00:06 5 | 700 | 2022-03-01 12:00:07 2 | 200 | 2022-03-01 12:00:08 9 | 100 | 2022-03-01 12:00:08 On every X seconds, query should be run, and it should process 5 records where column C > LAST_PROCESSED_TIMESTAMP. This LAST_PROCESSED_TIMESTAMP is updated after each run. What I need is - I want to select these 5 rows, but not to include the rows if columns A and B are going to repeat in some fetches that are going to happen in the future. Example: for table above: First run - select 5 1 | 100 | 2022-03-01 12:00:00 2 | 200 | 2022-03-01 12:00:01 = '2022-03-01 12:00:00' LIMIT 5 ) a LEFT JOIN ( SELECT A,B FROM TABLE WHERE C >= '2022-03-01 12:00:00' LIMIT 5, 18446744073709551615 ) b ON ( a.A=b.A AND a.B=b.B ) WHERE b.A IS NULL; and also (this one is probably NOT OK, since it will select MAX of C even if not in first 5, so for my example, it would include 2 | 200 | 2022-03-01 12:00:08 inside of the first run - not what I need): SELECT A, B, MAX(C) FROM TABLE WHERE C >= '2022-03-01 12:00:00' GROUP BY A, B ASC LIMIT 5;

I have a table with two main columns, id1 and id2: drop table if exists pairs; create table pairs (id1 int, id2 int); insert into pairs values (1,2), (1,3), (3,1), (2,4), (5,4), (3,6), (7,8); I want to select rows which have these pairs [(1,2),(5,4),(3,1)]. This array could have more than 500 items, so how I can write a select query to select those rows? I am using 'Netezza' database but I need to find the logic to do this, I can figure out how to implement this logic in my database engine.

I need to do a find a replace but rather than find one word to replace with another I have about 16k words, so as an example; table 1 has two words apple,orange - table 2 has the contents of a book, i want to be able to replace all the words apple and orange that exists in table 2 and replace them with ##, is there a query that does this ? my actual table 1 has about 16k words and table 2 has 8 million words. most of the examples on google suggests i type the words to find but that is not possible when i have 16k words I am using mysql workbench and an absolute beginner (from comment) table 1 has 16k names in a column - table 2 has emails and text in columns - i want to be able to censor out all the names in table 2 using the names in table one as a reference

I have a table datedim which has all the dates of a month in it. I have another table attendance that has dates on which attendance were taken. I need to write a query where i can get all the dates from the start till current. For eg. if attendance table has records starting from 3-Apr-2019 to 3-June-2019, i should get all the dates in between them.

I have been trying to make a new table and want to use the date of the existing data. Can anyone help me understanding what is wrong with this formula?

dDate = CALENDAR(DATE(YEAR(MIN(Ftransactions[Date])),1,1),DATE(YEAR(MAX(Ftransactions[Date])),12,31))

The error which keeps popping up after pressing enter is: > An argument of function 'YEAR' has the wrong data type or the result is too large or too small. If the argument is expected to be a date, that date must be between January 1, 100 and December 31, 9999.

I have a table named 'mrtg' that has four columns: mysql> select * from mrtg; +----------+------+------+------+ | city | day1 | day2 | day3 | +----------+------+------+------+ | Delhi | 2 | 6 | 9 | | Mumbai | 1 | 3 | 8 | | Banglore | 4 | 1 | 6 | +----------+------+------+------+ 3 rows in set (0.00 sec) If current date = '2019-09-01' then I want to select as SELECT CITY, DAY1 FROM MRTG; If current date = '2019-09-02' then I want to select as SELECT CITY, DAY1, DAY2 FROM MRTG; If current date = '2019-09-02' then I want to select as SELECT CITY, DAY1, DAY2, DAY3 FROM MRTG; and so on. How can I write a query for this? The values represent day-wise data for a whole month. I do wonder about this type of table structure, but it's the client requirement. I need to find a solution to fetch only the columns from day 1 to the current date.

I have a main table set up like this: |id |table_to_join | table_item_id | |:---|:-------------|:--------------| |1 |products |123 | |2 |products |577 | |3 |products |344 | |4 |products |1230 | |5 |images |14 | |6 |images |42 | |7 |video |555 | |8 |products |400 | |9 |video |9 | |10 |images |19 | I have three other tables called products, images and videos. Their Primary ids correspond to those of the table_item_id shown in the main table above, which is an indexed column. I would like to query the main table and select those 10 rows, and join those three tables to their corresponding table_item_id to produce one single list showing a variety of different media. I am looking for an efficient query to do something like this, if it's even possible. I had thought of three Joins, but that seems a bit messy, especially if I start adding more than the three tables in the future. I had also thought of using individual ajax queries on each table after it displays on the homepage, but again that seems a bit messy. The query below is sort of what I'm looking for, but I'm not sure if there is a better, more cost-efficient method that best fits what I'm intending to do. Let me know what you think. SELECT * FROM main JOIN products ON main.table_item_id = products.id AND table_to_join = 'products' JOIN images ON main.table_item_id = images.id AND table_to_join = 'images' JOIN video ON main.table_item_id = video.id AND table_to_join = 'video' LIMIT 10

Previously I had 4 default values for a jsonb column in my table cities.
create_table "cities", force: :cascade do |t| t.jsonb "config", default: {"A"=>5, "B"=>7, "C"=>10, "D"=>10} Now I want to add 2 more default values "E"=10 and "F"=0 to this column without changing the values of A,B,C and D. Note that the values of A,B,C and D have been updated differently for different cities. So I just want to append the default values of E and F to the column without changing the existing ones of A,B,C and D. I cant't quite figure out the UPDATE query for this. I am quite new to this so its a little confusing. Thanks in advance for your help.

Below is my query. i get an error msg. it works when i remove the fields without the sum. I have added a screen shot of the data. Thanks! SELECT S.PRODUCT_ID, SUM(DISTINCT S.AWARD_AMT), SUM(DISTINCT S.EST_AMT), SUM(PMT.PMT_AMT), S.CREATE_DT, S.APPROVE_BY, S.EVALUATOR FROM SALES S INNER JOIN INVOICE INV ON S.PRODUCT_ID = INVS.PRODUCT_ID INNER JOIN PAYMENTS PMT ON INV.S.PRODUCT_ID = PMT.S.PRODUCT_ID AND INV.SEQ_NO = PMT.SEQ_NO WHERE CAST(CREATE_DT AS DATE)>='01-JAN-2014' AND INV.STATUS_CD = 'CREATE' GROUP BY S.PRODUCT_ID order by S.PRODUCT_ID This was my final query. It worked. I had to remove the dups in excel SELECT S.PRODUCT_ID, S.CREATE_DT, S.APPROVE_BY, S.EVALUATOR , SUM(DISTINCT S.AWARD_AMT) OVER (PARTITION BY S.PRODUCT_ID) AS S.AWARD_AMT, SUM(DISTINCT S.EST_AMT) OVER (PARTITION BY S.PRODUCT_ID) AS EST_COST_TOT, SUM(PMT.LINE_AM PMT.PMT_AMT T) OVER (PARTITION BY OM.O_OMO_NO) AS TOTAL_PMT FROM SALES S INNER JOIN INVOICE INV ON S.PRODUCT_ID = INVS.PRODUCT_ID INNER JOIN PAYMENTS PMT ON INV.S.PRODUCT_ID = PMT.S.PRODUCT_ID AND INV.SEQ_NO = PMT.SEQ_NO WHERE CAST(CREATE_DT AS DATE)>='01-JAN-2014' AND INV.STATUS_CD = 'CREATE' and SUBSTR(APPROVE_BY,1,3) IN ('PEP','SMI', 'MOP','3/4') GROUP BY S.PRODUCT_ID order by S.PRODUCT_ID ERROR msg:ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause: *Action: Error at Line: 1 Column: 91

I have a table like this CREATE TABLE users_search_activity ( ID bigint(20) UNSIGNED NOT NULL, user_id int(11) NOT NULL, country_id int(11) NOT NULL, search_keywords text COLLATE utf8mb4_unicode_ci NOT NULL, date datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table users_search_activity -- INSERT INTO users_search_activity (ID, user_id, country_id, search_keywords, date) VALUES (1, 132, 2, 'xavie', '2021-07-13 08:20:37'), (2, 132, 6, 'xavier', '2021-07-13 08:21:38'), (3, 132, 5, 'xavier ins', '2021-07-13 08:21:39'), (4, 132, 4, 'xavier ins', '2021-07-13 08:21:39'), (5, 131, 9, 'xavier ins', '2021-07-13 08:22:12'), (6, 132, 7, 'xavier ins', '2021-07-13 08:22:25'), (7, 132, 8, 'xavier ins', '2021-07-13 09:24:43'), (8, 132, 6, 'xavier ins', '2021-07-13 09:24:45'), (9, 132, 4, 'xavier insa', '2021-07-13 09:24:47'), (10, 131, 5, 'ins', '2021-07-13 09:24:54'), (11, 132, 3, 'ins', '2021-07-13 09:24:54'), (12, 132, 2, 'ins', '2021-07-13 09:24:58'), (13, 132, 9, 'ins', '2021-07-13 09:24:59'), (14, 132, 0, 'ins', '2021-07-13 09:25:00'), (15, 132, 0, 'ins', '2021-07-13 09:25:02'), (16, 132, 0, 'inst', '2021-07-13 09:58:20'), (17, 132, 0, 'inst', '2021-07-04 09:58:25'), (18, 132, 0, 'inst', '2021-07-07 09:58:25'), (19, 132, 0, 'inst', '2021-07-11 09:58:26'), (20, 1, 12, 'University Business Academy in Novi Sad', '2021-07-14 10:16:33'); -- -- Indexes for dumped tables -- -- -- Indexes for table users_search_activity -- ALTER TABLE users_search_activity ADD PRIMARY KEY (ID); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table users_search_activity -- ALTER TABLE users_search_activity MODIFY ID bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20; COMMIT; Now I want to make some query from where I can get the data group by country_id and date. So for that I have made my query like this SELECT COUNT(*) OVER (PARTITION BY country_id, DATE(date)) AS count, user_id, country_id, DATE(date) as date, group_concat(search_keywords) FROM users_search_activity group by user_id,country_id,DATE(date) This is working fine MySQL 8+ but can someone tell me how to make this query so that it would work with older version of MySQL also? Any help and suggestions would be really appreciable. **Update** Here is the error what I am getting when trying the query with MySQL 5.7 19 errors were found during analysis. An alias was previously found. (near "count" at position 62) An alias was expected. (near " " at position 61) Unrecognized keyword. (near "count" at position 62) Unexpected token. (near "," at position 67) Unexpected token. (near "user_id" at position 74) Unexpected token. (near "," at position 81) Unexpected token. (near "country_id" at position 83) Unexpected token. (near "," at position 93) Unrecognized keyword. (near "DATE" at position 95) Unexpected token. (near "(" at position 99) Unrecognized keyword. (near "date" at position 100) Unexpected token. (near ")" at position 104) Unrecognized keyword. (near "as" at position 106) Unrecognized keyword. (near "date" at position 109) Unexpected token. (near "," at position 113) Unrecognized keyword. (near "group_concat" at position 115) Unexpected token. (near "(" at position 127) Unexpected token. (near "search_keywords" at position 128) Unexpected token. (near ")" at position 143) #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY country_id, DATE(date)) AS count, user_id, country_id, DATE(d' at line 1 But with MySQL 8.1 I am not getting any errors.

I have three tables: Records, ChildTable, and ParentTable. ChildTable and ParentTable both have RecordId as the foreign key. My aim is to delete all records older than 300 days from the Records table, along with corresponding entries from the other two tables. The Records table contains nearly a million records to be deleted, while ChildTable and ParentTable collectively hold around 3 million rows for all the RecordIds being deleted. I'm executing these deletions in batches of 4000 and would like to verify if the logic I'm employing is correct. Any assistance would be greatly appreciated. Thank you!

Declare @errorMessage	Varchar(1000)
Declare @counter		Int = 0
Declare @BatchSize		Int = 1000

Set NoCount On

WHILE (1=1)
BEGIN
	Begin Try
		Begin Tran

			/****** Get all the deletions from the records  ******/
			Select Top (@BatchSize) RecordId
			  Into #TempDeletions
			  From [dbo].[Records] With(NoLock)
			 Where LastDate  0
			Rollback Tran

		Select Error_Message()
	End Catch
END

I've been trying to learn CTEs and having a bit or trouble getting the hang of them. I wrote a query against the Sakila Sample database that lists information about horror movie rentals for each day. Here is the overblown (and redundant) SQL that I came up with: SELECT CONCAT(CU.last_name, ', ', CU.first_name) AS customer, A.phone, F.title, date(R.rental_date) AS rental_date FROM sakila.rental R LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id LEFT JOIN sakila.film F ON I.film_id = F.film_id LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id LEFT JOIN sakila.category C ON FC.category_id = C.category_id LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id LEFT JOIN sakila.address A ON CU.address_id = A.address_id WHERE CU.customer_id in (SELECT CU.customer_id FROM rental R LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id LEFT JOIN sakila.film F ON I.film_id = F.film_id LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id LEFT JOIN sakila.category C ON FC.category_id = C.category_id WHERE C.name = "Horror" GROUP BY CU.customer_id HAVING COUNT(CU.customer_id) >= 3) AND C.name = "Horror" ORDER BY customer, title, rental_date DESC; And here are some of the results in my database client (Navicat for MySQL ): **Is there a way to rewrite the query using a CTE?** It seems like just the type of query that a CTE would be perfect for, if only I could figure it out! Thanks!

I am using MySQL 8.0.23 I have 4 tables: houses, intervals, discounts, and availability. **Houses:** create table houses ( id int auto_increment primary key, name varchar(255) null, occupancy tinyint null ); insert into houses (id, name, occupancy) values (1, 'Celopatra', 2); +----+-----------+-----------+ | id | name | occupancy | +----+-----------+-----------+ | 1 | Cleopatra | 4 | +----+-----------+-----------+ **Intervals:** create table intervals ( id int auto_increment primary key, house_id int null, start_date date null, end_date date null, adult_price int null, child_price int null, min_stay int null, max_stay int null ); insert into intervals (id, house_id, start_date, end_date, adult_price, child_price, min_stay, max_stay) values (1, 1, '2021-02-01', '2021-02-05', 40, 30, 2, 4), (2, 1, '2021-02-06', '2021-02-10', 50, 40, 2, 4), (3, 1, '2021-02-11', '2021-02-15', 60, 50, 2, 4); +----+----------+------------+------------+-------------+-------------+----------+----------+ | id | house_id | start_date | end_date | adult_price | child_price | min_stay | max_stay | +----+----------+------------+------------+-------------+-------------+----------+----------+ | 1 | 1 | 2021-02-01 | 2021-02-05 | 40 | 30 | 2 | 4 | | 2 | 1 | 2021-02-06 | 2021-02-10 | 50 | 40 | 2 | 4 | | 3 | 1 | 2021-02-11 | 2021-02-15 | 60 | 50 | 2 | 4 | +----+----------+------------+------------+-------------+-------------+----------+----------+ **Availability:** This table is an expanded version of the intervals table. I am using the PHP backend to generate days for each interval period to query for availability. However, the price column in this table is not related to adult_price, child_price in the intervals table. It is a minimum deposit price for each day. create table availability ( id int null, interval_id int null, date date null, price int null, is_available int null ); insert into availability (id, interval_id, date, price, is_available) values (1, 1, '2021-02-01', 100, 1), (2, 1, '2021-02-02', 100, 1), (3, 1, '2021-02-03', 100, 1), (4, 1, '2021-02-04', 100, 1), (5, 1, '2021-02-05', 100, 1), (6, 2, '2021-02-06', 120, 1), (7, 2, '2021-02-07', 120, 1), (8, 2, '2021-02-08', 120, 1), (9, 2, '2021-02-09', 120, 1), (10, 2, '2021-02-10', 120, 1), (11, 3, '2021-02-11', 130, 1), (12, 3, '2021-02-12', 130, 1), (13, 3, '2021-02-13', 130, 1), (14, 3, '2021-02-14', 130, 1), (15, 3, '2021-02-15', 130, 1); +------+-------------+------------+-------+--------------+ | id | interval_id | date | price | is_available | +------+-------------+------------+-------+--------------+ | 1 | 1 | 2021-02-01 | 100 | 1 | | 2 | 1 | 2021-02-02 | 100 | 1 | | 3 | 1 | 2021-02-03 | 100 | 1 | | 4 | 1 | 2021-02-04 | 100 | 1 | | 5 | 1 | 2021-02-05 | 100 | 1 | | 6 | 2 | 2021-02-06 | 120 | 1 | | 7 | 2 | 2021-02-07 | 120 | 1 | | 8 | 2 | 2021-02-08 | 120 | 1 | | 9 | 2 | 2021-02-09 | 120 | 1 | | 10 | 2 | 2021-02-10 | 120 | 1 | | 11 | 3 | 2021-02-11 | 130 | 1 | | 12 | 3 | 2021-02-12 | 130 | 1 | | 13 | 3 | 2021-02-13 | 130 | 1 | | 14 | 3 | 2021-02-14 | 130 | 1 | | 15 | 3 | 2021-02-15 | 130 | 1 | +------+-------------+------------+-------+--------------+ **Discounts:** Type 1 represents percent discounts, 2 represents fixed amount discounts. create table discounts ( id int auto_increment primary key, interval_id int null, discount_type int null, discount_amount int null, status int null ); insert into discounts (id, interval_id, discount_type, discount_amount, status) values (1, 1, 1, 20, 1), (2, 1, 2, 40, 1); +----+-------------+---------------+-----------------+--------+ | id | interval_id | discount_type | discount_amount | status | +----+-------------+---------------+-----------------+--------+ | 1 | 1 | 1 | 20 | 1 | | 2 | 1 | 2 | 40 | 1 | +----+-------------+---------------+-----------------+--------+ **Final goal:** I want to list available houses by their final price (after discount applied - discount may not occur as well) for the specific date period. To achieve that firstly I am looking for available houses for the given date, after that I am sending the following parameters: sum, minimum start date, house id, and day_count to the calculate function. Now, I was wondering is it possible to optimize this query. I thought, maybe instead of passing the min start date to calculate function, passing interval id directly would be much efficient. How would you implement the same functionality in a better way? What would you say about the performance of this query? You can view DDL and DML statements via this link CREATE FUNCTION calculate(sum INTEGER, minStartDate DATE, house_id INTEGER, day_count INTEGER) RETURNS INTEGER DETERMINISTIC BEGIN DECLARE interval_id INTEGER; set interval_id = (select id from intervals s where s.start_date = minStartDate and s.house_id = house_id); SET sum = IFNULL((SELECT sum - sum * d.discount_amount / 100 FROM discounts d WHERE d.interval_id = interval_id and d.discount_type = 1), sum); SET sum = IFNULL((SELECT sum - day_count * d.discount_amount FROM discounts d WHERE d.interval_id = interval_id and d.discount_type = 2), sum); return sum; END; select house_id, sum(a.price) as price, count(a.id) as day_count, min(i.start_date), calculate(sum(a.price), min(i.start_date), house_id, count(a.id)) as final_price from availability a inner join intervals i on i.id = a.interval_id where a.date > '2021-02-03' and a.date < '2021-02-13' and a.is_available = 1 group by i.house_id having day_count = 9;