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 co…
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 ea…
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…
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…
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 WIND…
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 o…
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 sho…