How to get the whole row or other column value for the same row for which window function in over clause gave output. For ex. Using the above query I get output as the best value which defined the maximum number above me when order by timestamp. The output of the above column : I calculated the best value usi…
Tag: window-functions
Postgres Calculating Weighted Average
I am trying to get the weighted average of items from this example table: Item Value Item1 0.10 Item2 0.15 I followed this example here how to calculate it: https://www.wikihow.com/Calculate-Weighted-Average And tried this statement: I am trying to get the average by doing SUM(numberXWeightingFactor) but it d…
Get first and last Order and the highest value Item in each order for each Customer, all of which are separate tables
I need to find the first and last Order for each Customer by OrderDate, and the name and SKU of the item with the highest business volume in each of those orders. For reference, the Customer table has >150k records, and Orders and OrderDetails (these are the Items) a lot more. Note: Both Orders and their r…
How to remove and carry forward month prior when running balance decreases?
I have the following data: client_id balance_month balance 100000000 September 1,2021 12:00 AM $7000 100000000 August 1,2021 12:00 AM $7000 100000000 July 1,2021 12:00 AM $6000 100000000 June 1,2021 12:00 AM $3000 100000000 May 1,2021 12:00 AM $20000 100000000 April 1,2021 12:00 AM $1000 100000000 March 1,202…
How to make revenue $0 for months prior to decrease in SQL?
I have the following data: client_id balance_month balance 100000000 September 1,2021 12:00 AM $400 100000000 August 1,2021 12:00 AM $100 100000000 July 1,2021 12:00 AM $200 100000000 June 1,2021 12:00 AM $300 200000000 September 1,2021 12:00 AM $99 200000000 August 1,2021 12:00 AM $100 200000000 July 1,2021 …
How to add data to a field based on other fields in a SQL database
I have an SQLite table called wcvp constructed from a csv file downloaded from the World Check List of Vascular Plants (see https://wcvp.science.kew.org/ and http://sftp.kew.org/pub/data-repositories/WCVP/). When I run this query: I get this result: kew_id genus species infraspecies 304293-2 Quercus robur 771…
Count Distinct Window Function with Groupby
I have a table that contains a user’s name, market, and purchase_id. I’m trying to use a window function in SnowSql without a subquery to count the distinct number of purchases a user bought as well as the total number of unique purchases for the market. Initial Table User Market Purchase_ID John …
Combine like rows based on substring of one column?
I have a table of parts that looks like this: Part Part Num Thing1 Thing2 Thing3 Thing4 Door 10105322 abc abc Door 10105323 abc abc Door 10105324 abc abc Door 84625111 abc abc abc Door 84625118 abc abc abc Door 84625185 abc abc abc Door 56897101 abc abc The part number is always 8 characters. For many parts, …
SQL query to consider exactly last 3 email send count and then return the respective click count
I have a table – ‘clicks_plus_noClicks_raw’ like below If customer has not clicked on any of the last 3 emails we sent(exact 3), I want to add the result to final table with count as 0. a@gmail.com & b@gmail.com should be skipped because we dint send them 3 emails yet.The final results s…
Lead window function in mysql to find sales
Given this table. I would like to know for each day how many different customers made a sale on date t and and t+1. The result for date 2021-06-30 is 2 because customer 1 and 3 made a sale in t and t+1. Answer Use LEAD() window function for each distinct combination of date and customer to create a flag