I have function (from this question) which groups values by every 5 minutes and calculate min/avg/max: However, due to missing values, some five-minute periods are skipped, making the timeline inconsistent. How to make it so that in the absence of data for a certain period, the value of max / avg / min become…
Tag: window-functions
Find the average over the n last purchases, for every user in the table using SQL
I have a SQLite db with two tables: users userID field1 field2 field3 field4 1 2 3 purchases purchaseID userID price timestamp 1 2 70 5166323 2 1 30 6543654 3 1 100 5456434 4 2 30 5846541 5 2 40 9635322 6 1 50 2541541 I want to write an SQL query that returns a table userID field1 field2
Getting values based on other column
I have the following data in SQL. Is there a way using SELECT QUERY that we can replace the NULL values in DATE column based on the REF values? Like replace the NULL values with the first available date for matching REF value, without making any change to the database. Expected Result Answer You can do it wit…
Find two values of one Column based on value of another column per ID
I have a sqlite db with one table that called Loan. This table with sample data is here: Loan Table at sqlfiddle.com This table contains below Columns: Now, I need a query to show desired result, contain [empid],[Codepayid],[Lval-1],[Lval-2],[Sum(Lint)],[Lrmn-1],[Lrmn-2], With this Conditions: For example: Re…
Display total where row number is max row_number
I have a query that displays invoice information, and in final column displays the Supplier’s total balance, I only want the total to be displayed on the last line of that supplier’s invoice entries. ie. the results may contain 100 invoices for say 20 suppliers, each supplier having a different nu…
sql that finds records within 3 days of a condition being met
I am trying to find all records that exist within a date range prior to an event occurring. In my table below, I want to pull all records that are 3 days or less from when the switch field changes from 0 to 1, ordered by date, partitioned by product. My solution does not work, it includes the first record
Using SQL, when there are multiple columns with dates. how can I determine and return a row where the date is between the other rows?
I am building a table for a data warehouse that needs to have a row for each change that occurs. The issue is that there are sometimes changes that occur in the subgroups and I can’t figure out how to show those changes. For example, I have the following table: RowNumber Code CorrectedProductYear Produc…
SQL to find records with last 3 same status
I have the following tasks table: I need to run a query that returns only the types which their last 3 tasks finished with status “FAILED”. How can I do this? Should I use a window function? Thanks Answer You can use aggregation and filtering after using row_number() to get the last three rows: Ac…
Why does Hive throw me an error while using Order by date?
I am trying to write a query In hive and I am seeing the following error. “Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies. Underlying error: Primitve …
How to express “either the single resulting record or NULL”, without an inner-query LIMIT?
Consider the following query: Assuming col1 is non-NULLable, this will yield either true or false – or possibly NULL when table1 is empty; But now suppose I have: (and assume col2 is also non-NULLable for simplicity.) If there is a unique col2 value for the lowest col1 value, or the table is empty, then…