I have a fairly complex query which results in a list of Customer Ids where they have not ordered within a defined period, have no Rep assigned to them, and are business customers rather than private. This simply returns a sequential list of Ids which match the BIGINT primary keys of the matching customers, like this: I now want to
Tag: common-table-expression
Using SELECT subquery in a CASE statement and handling NULLS
I have 2 SQL tables one holding stock information then a style table which details how the stock is used. For some jobs the information is contained with a print file so the value in the stock table would be BIN, if not it would contain a template name which would cause a query to a Styles table which then
SQL Updating Values of Column for Each Unique Value in Separate Column (without a loop)
I’m currently writing a query that is grabbing data from a table and I want to update one of the columns in that table. The update needs to be based off the distinct values from one column. For example: Type ID x ID1 x ID1 y ID2 y ID2 z ID3 z ID3 The catch here is that I can’t
SQL few running total at different stops
Note: I am bringing up a problem to which I have a solution, the purpose of the question is to see if there is a better one. I have customers that make transactions. I want for each customer to know when did they reach 3K, 5K and 7K (or any other threshold). End result is: I was wondering if I
Converting recursive CTE from Postgre SQL to SQL Server
I am having trouble adapting some recursive CTE code from PostgreSQL to SQL Server. Here is my table: and what I am trying to adapt: I have been having issues adapting the use of ARRAYs. Any pointers would be really helpful! Answer Much more quicker will be : Tested with 100003 rows, the results are : Charlieface Query 1 :
Executiung performant SQL queries equivalent to “nested deletes”
Consider the following ERD for Order, Item and Article entities: I’d like to delete Orders satisfying a complex condition, and then delete Items associated to those Orders, and finally delete Articles associated with those Items. Cascade deleting from Orders to Items is possible as Order is a parent of Item. But cascade deleting from Item to Article isn’t possible as
PostgreSQL: Using CTE with IN
Learning CTE right now, the following query is super basic and it doesn’t really have any useful value but I don’t understand why it doesn’t work The error I’m getting is “ERROR: column “cte_actors” does not exist” Currently using postgres 14 and DBeaver Answer You need to SELECT from the CTE:
“WITH AS” Working in Postgres but not in H2 dabatabse
I am writing a single query to insert data into 2 tables using “WITH AS”. The query works fine on Postgres but on H2 database it is throwing syntax error. I have 2 tables. Table 1 has 2 columns — a Primary Key table1_ID and a table1_value column. Table 2 has 3 columns — a PK table2_Id and table2_value and
How to pipe window function output directly into a new window function in SQL?
I am new to SQL and I have the following query: This doesn’t work due to no such column: MyMax. Even though from my understanding the column for MyMax is being created on the fly, I’m guessing SQL still isn’t able to use its values immediately as an input into the next window function I already tried creating the column
How to recursively calculate rows based on previous rows in Snowflake?
I’m trying to build a performance score on each player in my table and use this to run some analysis. I’ve tried to use the Elo ranking using the player’s score in the game vs themselves in the prior game. The game is a time-trial race so the faster they complete the better their performance. I simulate a win (1)