I want to fill NULL values in device column for each session_id with an associated non-NULL value. How can I achieve that? Here is the sample data: +————+——-+———+ | session_id | step …
Tag: window-functions
Using columns in window function that are not in Group By, I get : SQL compilation error: [COLUMN_A] is not a valid group by expression
I’ve seen a lot of questions about this error, the closest one (as it’s using window function) to my issue would be this one My issue is that I use columns in the windows function that are not …
Database search results with context
I’ve built a system for archiving, aggregating, and indexing my IM history across different platforms (e.g. MSN Messenger, Hangouts, WhatsApp). The data is currently stored in an SQLite3 database, and relevant parts of the schema look something like this: There are also NOT NULL constraints on all colum…
How to use over – partition by query in SQL in order to get the current, average, and maximum value?
I have this table where it shows a point that was being done by a device at an area and specific location. I want to get the current, average and also maximum points grouped by area and also location. If I choose any day, the current qty will show the quantity of the latest working date. Meanwhile, average qu…
how to select third, fourth, fifth date in sql using window function
The table rental of movies has this data: how to generate pairs of : first rental_ts, second rental date, second rental, third rental date, …. second LAST rental date, last rental date, last rental date, null? using window functions- this is not working: i expect: basically i need more than 1 level back…
How to select TOP records in Postgres. Not a similar question
I have a table with three columns (City, Orderid, Total_quantity) and more than 500 rows. I want to fetch the top 50 records according to total_quantity wise. I can do this with rank_number but the …
Daily forecast on a PySpark dataframe
I have the following dataframe in PySpark: DT_BORD_REF: Date column for the month REF_DATE: A date reference for current day separating past and future PROD_ID: Product ID COMPANY_CODE: Company ID CUSTOMER_CODE: Customer ID MTD_WD: Month to Date count of working days (Date = DT_BORD_REF) QUANTITY: Number of i…
Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns
I have a dataset with booking hotels. date_in has format “yyyy-MM-dd”. I need select top 10 the most visited hotel by month. I get the following error: Error: Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only …
How to split dates into groups of 24 hours -BigQuery
this is my goal, split the dates into groups of 24 hours, but those depends of the data, not of a very specific date Let’s say these are the calls from a call center, I want to know how many sessions I had, but those sessions are valid for 24 hours, those 24 hours are starting to count since the
I am trying to provide a ranking based on a previous partition
I am trying to change the ranking column where instead of ordering by RMSE ASC I want to order by RMSESum ASC (which was a previous partition by)….when I do this I get an error that “ordered analytic functions cannot be nested” any idea around this? Answer You should use the sub-query as follows:…