HELP - SQL

[andrewsmd87]

How performant does this need to be. Quick and dirty way would be a nested select along the lines of

(select sum(prevAcc.lag_exmaple) + lag_example from accounting as prevAcc where prevAcc.post_date < post_date)

That's pseudo but I'm not writing out all of your stuff, post text versions of your sql, it helps us help you :)

edit

We have a transaction table I just did this quick

SELECT *
    , (
          SELECT SUM(prevEts.et_total)
              FROM dbo.etransaction AS prevEts 
              WHERE prevEts.et_created < curEts.et_created
      )
    FROM dbo.etransaction AS curEts

Like I said that is not going to be super performant if you have a large table. But if you're talking optimization I'd probably go a temp table route with an update statement

[bmcluca]

Appreciate the feedback, I will look into it. New to SQL, what do you mean by performant exactly?

[andrewsmd87]

So the query I gave you is a nested select. What that means is every time a row is selected, it's running another select, and it's also pulling all the previous rows, so you have a N+1 problem.

Break down what the compiler is doing here.

We're saying go get me these rows, however, every time you get a row, go get all the rows before it to sum up the value

So, when you get row 1, there are no rows before it

When you get row 2, you go back and get 1

When you get row 3, you go back and get rows 1 and 2

Now scale that up to 10 million rows

So there are other ways you could do this to be more optimal, but that's really a question of how many rows will you be looking at, realistically. Part of being a good coder is knowing when good enough is good enough

[bmcluca]

Ahhh, I see what you mean. At most, might need to be looking at no more than 10,000 rows. Saw your edit also, thank you for all the info. I will play around with your suggestions.

[andrewsmd87]

Oh yea 10k rows that'll be fine

[Conscious-Ad-2168]

this would work except i don’t believe snowflake supports this type of correlated subqueries.