I am having trouble getting my DENSE_RANK() function in Oracle to work how I would like. First, my dataset: I am trying to use the dense rank function to return results with a sequence number based on the DATE field, and grouping by ID. How I want the data to return: The query I have so far: However, this ret…
Oracle Query With (BETWEEN Two Dates) Returns Empty Result Set
I have an Oracle SQL Query I am trying to run, but it keeps returning null value for the second query below but when I use the first query it returns a value. Please can someone help me check what I may be doing wrong in query 2? Note that the date column is in the Format 21/09/2020 10:00:00 AM
SQL Spark – Lag vs first row by Group
I’m SQL newbie and I’m trying to calculate difference between the averages. I want for each item and year calculate difference between months, but I want always substract current average – fist month …
What happens when one aggregates without the ‘group by’ clause?
I was wondering why this SQL script SELECT category, avg(valuation) FROM startups group by 1; Gives me the same output like this one: SELECT category, valuation FROM startups group by 1; And why is …
Returning one result per id in SQL in a joined table
I have one table of users. users | user_id | name | | ——- | —— | | 1 | Jerry | | 2 | George | | 3 | Elaine | | 4 | Kramer | I have one table that links roles to …
Create subset using select in R
I am new to R scripting, I need to create subset of dataset using select function with some condition, I need just two columns not all columns. This is my code sqldf(‘SELECT * from dataset WHERE …
How to accurately pull minimum date of each time a payment method was changed
I need to get the minimum date for each time a payment method changes between the method id, account number, or routing number. I’m bouncing between a cursor and between windows functions. Doing the method below seems to work fine, but when i use this method with a larger dataset, with different pay ids…
COALESCE types jsonb and boolean cannot be matched
I have next part of query: ‘goals’, COALESCE( json_object_agg(log.type, COALESCE(log.data, TRUE)) FILTER (WHERE log.type IS NOT NULL), ‘{}’ ) and I am getting next error: …
Storing struct_time in SQL
Some code I am writing in Python takes in a date from a server in the struct_time format (with the 9 args). How can I store this date in an SQL database, and be able to read back this date as a …
Filtering multiple XML nodes using XPath in PostgreSQL
There is an XML with the structure: – Item – Documents – Document – Records – Record – Category – Code – Value And here is the SQL query that …