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 customer made in
Tag: window-functions
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 already tried creating the column
Oracle SQL: Dividing Counts into unique and non unique columns
I have a table that looks like this: I want to aggregate by FileID and split the File Info column into 2 separate count columns. I want 1 column to have the count of the Unique File Info and the other to be a count of non-Unique file info. The result would ideally look like this: where the non-unique count
Invalid group by expression error when using any_value with max and window function in Snowflake
I was given a query and I am attempting to modify it in order to get the most recent version of each COMP_ID. The original query: I then attempted to use a window function to grab only the highest version for each comp_id. This is the modified query: When attempting to compile the below error is given: SQL compilation error:
Find groups containing 6 consecutive 1s in one column
I have a table with 2 columns: val with values: 0 or 1 id with unique identifiers How do I to find id with 6 values = 1 in a row. In the example above: id = 9, id = 10. It is desirable not to use loops (cursors or while), but something like sum(…) over(…). Answer You can use
SQLite – calculate percentage increase in values of a numeric column
I am interested in getting the % increase or decrease of the values in one column with respect to the previous value. Can someone please advice how I should do this? Thanks This is what my table looks like And this is what I would like Answer Use LAG() window function to get the previous value of sales for each
How to carrying over values for missing dates in time series using last value windows analytical functions in mysql
How to carrying over values for missing dates postcode/indicator_category to create full monthly time series. Im trying to use last_value to carry over values but not able to make it. Is my approach correct? Any help would by highly appreciated. Example given a table: INSERT INTO value to indicator_data table INPUT: postcode month_ts indicator_cat measure sw5 2017-07-01 2 99212.231 sw5
Finding the most visited place at a particular time in SQL
I have a user’s table that has info about user_id, places user purchased tickets, and the time user had purchased the ticket. Users: I want to add a new column that shows the user’s most visited place at the time of purchasing ticket. The table would like (Snowflake): Answer You want to use a WINDOW version of COUNT to get