I have a login table with columns UserID and login_date like below (but with thousands of entries). How would I find the longest streak of consecutive log-ins for each user and what the start and end days were for those streaks? Edit: Using Postgres. Removed question about streaks only counting M-F and not we…
Tag: common-table-expression
Get all ancestors of a child in postgres recursive query
I am trying to get all related ancestors of a child with more info by joining with another table. I’m kind of new to backend so recursive cte is hard to understand. Find SQL Fiddle here Data I have : product_id,user_id Data I need : user_id master_id cost id(john_snow) null 4 id(bran_stark) id(john_snow…
Does Oracle always resolve CTE with clauses even if they are not used in the result set
If I have an Oracle SQL query like this: Will the DBMS actually do the work of resolving/running query2, or does Oracle know that query2 is not required by the final output, so the work of that CTE/with should be skipped? Oracle version is 12c Enterprise. Answer I was going going to say “it’s up t…
‘WITH’ clause does not work with in operator
I have a simple query: It should create a MaxSal temporary table and then in the WHERE section it should check whether the Salary, DepId are inside said table. Unfortunately, this query gives me ORA-00920: invalid relational operator which I guess is referring to the in operator. If I put the Sub-query direct…
Convert CTE Query into normal Query
I want to convert my @PostgreSQL, CTE Query, into Normal Query because the cte function is mainly used in data warehouse SQL and not efficient for Postgres production DBS. So, need help in converting …
How to retrieve the properties stored in SQL with multiple inheritance
I’m storing the records in SQL that represent a multiple inheritance relationship similar to the one in C++. Like that: CREATE TABLE Classes ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); …
How to specify a limit on Postgres json_agg
I want a JSON output having the distinct values of a column and also a limited number of rows. This is the sample table that I have in a Postgres Database: Name Author Copies Sold —- …
Calculate cumulative product value
I have the following database table: Date Return Index 01-01-2020 0.1 Null 01-02-2020 0.2 Null 01-03-2020 0.3 Null I would like to update the Index value using the following …
Split text into multiline chunks based on fixed character length using SQL
I am looking for SQL that takes a string of text (coming in as up to 3 lines, but usually 1) and breaks it into at most 3 lines with a max of 30 characters per line. (The background is I’m trying to insert text into address line fields and sometimes the incoming values are longer than 30 characters and
How to go convert to CTE
I am a little confused out a CTE works. I need to convert my statement from a rollup with a UNION to a CTE… but the CTE is a bit confusing me. Any Ideas how to approach this? Answer You can express this using a CTE as: