python (12.9k questions)

javascript (9.2k questions)

reactjs (4.7k questions)

java (4.2k questions)

java (4.2k questions)

c# (3.5k questions)

c# (3.5k questions)

html (3.3k questions)

Questions - common-table-expression

CTE Fast To Select from but Slow to Insert from

I've got a CTE that does a lot of hard work over tens of thousands of records. If I just run the select below which uses the CTE it comes back in less than a second. However if I add the insert line a...
test-img

fosbie

sql-server

common-table-expression

Votes: 0

Answers: 1

Latest Answer

Thanks Golden Lion, I tried replacing the CTEs with temp tables instead and it's lightning fast. Not sure I understand why but I guess I'm sorted now. Thanks everyone for your help.
test-img

fosbie

how to store cte and use it inside another cte in sql server

im trying to do cohort analysis using SQL server i was looking at this online `-- (user_id, cohort_month), each with cohort_items as ( select date_trunc('month', U.timestamp)::date as cohort_mo...
test-img

Kaokor

sql

sql-server

tsql

common-table-expression

Votes: 0

Answers: 2

Latest Answer

with cohort_items as ( select date_trunc('month', U.timestamp)::date as cohort_month, id as user_id from users U ) ,user_activities as ( select A.user_id, MONTH_DIFF( date_...
test-img

Kazi Mohammad Ali Nur Romel

Improve query to get most games won

Here is the problem as follows: team season games_won San Francisco Giants 2010 92 San Francisco Giants 2011 86 San Francisco Giants 2012 94 San Francisco Giants 2013 76 San Francisco ...
test-img

Alexis Moreno

sql

sql-server

optimization

subquery

common-table-expression

Votes: 0

Answers: 1

Latest Answer

I think you are probably really asking for which team had most wins PER SEASON, not just the most wins... For that, you might be best with over/partition something like select PQ.* from ...
test-img

DRapp

Unable to left join within a SQL query containing CTEs

I need to combine the results from two different CTEs into a single table. Hence, I have written the following SQL query wherein I attempted to create a left join on two different fields (patient_id, ...
test-img

veg2020

sql

join

snowflake-cloud-data-platform

common-table-expression

Votes: 0

Answers: 1

Latest Answer

It appears in my original code, I inadvertently omitted a comma after a.YR_QTR. Adding that as shown below, fixes the issue! a.UNIV_MBR_ID, a.YR_QTR, (IFNULL(op_clms, 0)) AS op_clms, (IFN...
test-img

veg2020

Posts

Questions

Blogs

Jobs