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
Tag: window-functions
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 get the first row per group?
I have a query like this: The result looks like this: Now I want to get the first row for each category_id. So it must be the biggest num and its business_id. So the expected result would be: How can I do that? Answer if your MySQL version support ROW_NUMBER + window function, you can try to use ROW_NUMBER to
SQL to find sum of total days in a window for a series of changes
Following is the table: start_date recorded_date id 2021-11-10 2021-11-01 1a 2021-11-08 2021-11-02 1a 2021-11-11 2021-11-03 1a 2021-11-10 2021-11-04 1a 2021-11-10 2021-11-05 1a I need a query to find the total day changes in aggregate for a given id. In this case, it changed from 10th Nov to 8th Nov so 2 days, then again from 8th to 11th Nov so
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
Finding shortest geo-spatial distance from one point to all other points in SQL
There are two types of users who purchase the movie tickets from either town A, town B, town C or online. I have the following tables as: Locations: This table consists of locations of movie centers Users: This table contains the history of user’s purchase i.e. either online or in towns. Also consists of user’s latitude/longitude during the purchase. I
Get the newest two line per product and get price and date
I have made a query that extract the two newest lines per product. Each row shows id, productnumber, pricechangedate, price. Id Prod number Date Price Rank Order 71582 0071807993 2021-10-15 18:06:22 220.79 1 60533 0071807993 2021-10-15 13:22:46 220.79 2 Is it possible to some how concatenate these rows to show: Prod number Newest Date Newest Price Second Newest Date Second