Postgres SQL gathering data from two tables using an id from a 3rd table Problem Description: I have 3 tables; idmaster data1 data2 idmaster has every id. I need to count how many times each id exists in both data1 and data2 and output them seperate. id comes from idmaster. Ideally would like to have … Read more
Ranking and finding the difference in dates in a table in SQL Problem Description: I want to find the number of days between two different dates in a column which I have ranked based on the most recent date. I was able to write the code that gave me the correct difference in dates between … Read more
Sql Query + fetch first 2 records of every month Problem Description: I have a large set of data with the date as the primary key and there will be records for most of the days. Is it possible to write an SQL query to fetch records for the first 2 days of every month? … Read more
Oracle SQL – Grouping based on time within one second Problem Description: Sample Table: create table sampledata as select 78328696 pkid, 12848815 customer_id, to_date(’10/19/2022 11:05:38 AM’,’MM/DD/YYYY HH:MI:SS AM’) actdate, 0.5 units, to_timestamp(’19-OCT-22 11.05.38.947750000 AM’) datetime from dual union all select 78328697, 12848815, to_date(’10/19/2022 11:05:39 AM’,’MM/DD/YYYY HH:MI:SS AM’), 0.5, to_timestamp(’19-OCT-22 11.05.39.024819000 AM’) from dual union all … Read more
Replicate a case when statement in Jinja Problem Description: I want to replicate a simple case-when statement with a jinja block in dbt. How can I achieve this? Here is my statement: CASE status WHEN 0 THEN ‘pending’ WHEN 1 THEN ‘ordered’ WHEN 2 THEN ‘shipped’ WHEN 3 THEN ‘received’ WHEN 4 THEN ‘delivered’ ELSE … Read more
SQLAlchemy 2.0 `with engine.connect() …` does not automatically commit Problem Description: I’m currently experimenting with the new way to execute statements using sqlalchemy and updated it to 2.0.0.b4. According to the documentation I shall not use a string as an input variable for the execute function: Deprecated since version 2.0: passing a string to Connection.execute() … Read more
How to select last created record in a group by clause in mysql? Problem Description: I am using mysql 8.0.23 I have three tables, chats, chat_users and chat_messages I want to select the chat_id, the last message (with maximum createdAt date for a particular group. Said in other words, the message order by created_at desc … Read more
Generate a sequence in SQL based on the column values Problem Description: I need to generate a sequence in SQL Server 2016 database based on the following logic. I have three fields each represents ID of Brand, Category and the Product. A brand could have multiple categories and each category could have multiple Products. I … Read more
Postgresql- remove the brackets in the table Problem Description: I sorted the values of a column, now i want remove the brackets inside {} bracktes. The following code is for the sorting and updating it in: FOR _reference, _val IN select reference, categories from responses LOOP _array = (select (array_agg(p order by p.a )) from … Read more
Is there a way to shorthand this CASE statement in Snowflake/SQL? Problem Description: Have this case statement here that I want to use with both "CASE WHEN" and CASE ILIKE" similar to below: SELECT CASE coalesce(lower(regexp_substr(random_sample_column, ‘/key_word/\d+/([\w-]+)(/|$)’, 1, 1, ‘e’, 1)), random_sample_column) when ‘green’ then ‘GO’ when ‘red’ then ‘STOP’ when ‘yellow’ then ‘SLOW’ else … Read more