I have a dataset like the above. Basically it’s like a website session which I have a start + end activity, and in between there could be other unrelated records. I need to pull the records based on: I need to report all the “end” activity from the table In each “end” record, I n…
Tag: window-functions
Number of Customer Purchases in Their First Month
I have a list of customer orders. I can easily calculate the month and year of first purchase for each customer (e.g. customer 1 had their first purchase in Sept 2021, customer 2 had their first purchase in Oct 2021, etc.). What I want to add is an additional column that counts the number of purchases a custo…
Select highest aggregated group
I’m having trouble with selecting the highest aggregated group. I have data in a table like this: Sales table: ID GroupDescription Sales 1 Group1 2 1 Group1 15 1 Group2 3 1 Group3 2 1 Group3 2 1 Group3 2 2 Group1 2 2 Group2 5 2 Group3 3 2 Group4 12 2 Group4 2 2 Group4 2 I want
Window Function w/ a case statement
enter image description hereHelp for a newer SQL analyst: I need to get MAX value from the YearMo column by company, where Prem is zero/null. I created a flag to find YearMo where the sum is 0, indicated by a ‘1’ in the exclude column. How can I edit my current window function to include a case st…
How to calculate rolling timestamp sum from table partitioned by specific column? – SQL
I have a table with a series of timelines that are normalized starting from 00:00:00.00000. I want to summate them sequentially and stitch them together based on my order_key value. Sample Data: Desired Output: My Attempt: Answer Consider below query: Recursive Approach Non-recursive Approach
Modifying records in one column of a table conditioning on the date ranges in the other table
I am currently trying to combine Left join with window function (e.g. partition by class_id order by date_to desc) to solve the following problem in SQL. It is quite a difficult problem due to a bunch of tricky constraints that come into play, and it seems to me I need to have a way to choose which rows from …
SQL: partition by / window function with the combination of 2 columns, separated by todays date
I am trying to create a materialized-view in postgres, where I sum 2 columns, depending on todays date within a window function. In the below picture there is an example of the query I am trying to achieve: If today is the ‘2022-06-06’: 2022-06-05: 1+2+3+4+5 2022-06-06: 1+2+3+4+5+6+107 2022-06-07:…
Sum of Current + Previous X Nth Rows
I am looking to find the sum of the current + the last X Nth Rows. I am able to do this with the following query, however it is not very scalable. If this example, I’m finding the current value of “amount”, plus the last 3 “amounts” split 6 apart: X = 3 N = 6 I will be using
There are duplicate records in table, we need to select only the latest records as per date using group by or window function in sql
There are duplicate records in table, we need to select only the latest records as per date using group by or window function in sql code: Answer You can simply use a GROUP BY like so If you want to use ROW_NUMBER you have to PARTITION BY the id
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 alre…