I am trying to find the YoY growth of the users and trying to write a mysql query using window functions. Below is my query to find the lag : select substr(create_time, 1, 4) as date, count(*) as count, lag(count(*),…
I am currently migrating a PostgreSQL procedure to work in Redshift Spectrum Serverless. I was able to have a working procedure that works as intended in Redshift. However, its originally used inside a SQL select statement and I am currently…
i have a query in postgres which is taking 1 min and a few seconds to execute, found out that it's due to the order by clause. my understanding is that order by clause is one of the last to…
I have a query that gets quantity 1, value 1 , total 1 what I am trying to get is the following result quantity 1, value 1, total 1, sum quantity, sum total / sum quantity as value summ, sum…
Consider the following tables: create table `t1` ( `date` date, `value` int ); create table `t2` ( `date` date, `value` int ); insert into `t1` (`date`, `value`) values ("2022-01-01", 1), ("2022-03-01", 3), ("2022-04-01", 4); insert into `t2` (`date`, `value`) values…
Consider the following tables: create table `t1` ( `date` date, `value` int ); create table `t2` ( `date` date, `value` int ); create table `t3` ( `date` date, `value` int ); insert into `t1` (`date`, `value`) values ("2022-01-01", 1), ("2022-03-01",…
I'm facing a "Duplicate entry" error when trying to insert data into a MySQL table, even though the values being inserted are unique and should not violate any constraints. I have a unique constraint defined on a column, but the…
Table "ORDER": ID ORDERN_NUM 1 123 2 321 3 456 Table "RECEIVERS": FIRST_NAME ID LAST_NAME Pete 1 Tyler Sarah 1 Bowden Bart 2 Simpson Table "PRODUCTS": TYPE ID Towel 1 Pen 1 Spoon 2 We can say "FIRST_NAME" and "TYPE"…
Q: At present, I have the ID of table_b. How can I query the data corresponding to table_a? table_a id value 1 a1 2 a2 3 a3 table_b id table_a_ids 1 [1, 2] 2 [2, 3] It's not effective select…
I was asked this question in a job interview: There is a table with vehicle names mentioned in a column. Output when we check for name=car we must get as 4 i.e the maximum count of continuous occurrence of 'car'…