I have a set of data where value is calculated starting from 1 – 12 then moves 1 step down and calculates another 1 – 12. This works well in Excel however how do I achieve this in SQL. Is it even possible? I have tried which gives me a list of numbers then restarts after 12 so I want
Tag: window-functions
Cross Join in Hive
I’m trying to create a new column time_period while running the query below. If the date difference between a given transaction and the most recent transaction in the reference table is fewer than 7 days, then mark it as a recent transaction, else mark it as an old transaction. However, the query below is generating an error in the subquery
Select every second record then determine earliest date
I have table that looks like the following I have to select every second record per PatientID that would give the following result (my last query returns this result) I then have to select the record with the oldest date which would be the following (this is the end result I want) What I have done so far: I have
Count distinct per fiscal year and display all dates in query result
DB-Fiddle: CREATE TABLE customers ( id int auto_increment primary key, order_date DATE, customerID VARCHAR(255) ); INSERT INTO customers (order_date, customerID ) VALUES (“2020-01-…
Create a descending list of orders per item and display the ranking position in seperate column
DB-Fiddle Expected Result: In the results above I want to create a descending list of all orderIDs per itemID. Starting from the newest order to the oldest order which is defined by the event_date. The position of an orderID within a certain itemID should be displayed in column position. I tried to go with this query but could not make
SQL- Find the price of the smallest product in a store
I would like to find the price of the smallest product in a store and in addition, in another column, populate this price in all the products of the same store. Here is my table and the desired result in the “results” column: Table1 Here is my request but it does not populate the price: SELECT local ,product ,price ,IIF(MIN(Product)
SQL Server – Generate a column value which satisfy same condition in a day
I have a transaction table (Tran_Table) that contains Account Number, Transaction Date, Transaction Amount. I would like to generate a column that has “Yes” or “No” info in case if a transaction repeated in same day with same amount for same customer. Table and generated value example are shown below. how can i do this? Answer You can use window
SQLite – Rolling Average/Sum
I have a dataset as shown below, wondering how I can do a rolling average with its current record followed by next two records. Example: lets consider the first record whose total is 3 followed by 4 and 7 ,Now the rolling 3 day average for first record would be 4.6 and so on. Expected output: PS: Having “null” value
PostgreSQL Percent Change using Row Number
I’m trying to find the percent change using row number with PostgreSQL but I’m running into an error where my “percent_change” column shows 0. Here is what I have as my code. Here is my SQL table in case it’s needed. Thank you in advance, I greatly appreciate it. Answer You can use LAG() for your requirement: or you can
How to filter within a Window Function PostgreSQL
I only want to filter in Window function to keep the created_at is null so I don’t use WHERE clause previously. Because when I order by created_at desc it will show the null value first. How to add a …