Find total number of unique hackers who made at least submission each day (starting on the first day of the contest), and find the hacker_id and name of the hacker who made maximum number of submissions each day. If more than one such hacker has a maximum number of submissions, print the lowest hacker_id. The query should print this information
Tag: window-functions
Is there a way to change this BigQuery self-join to use a window function?
Let’s say I have a BigQuery table “events” (in reality this is a slow sub-query) that stores the count of events per day, by event type. There are many types of events and most of them don’t occur on …
Find the longest streak of perfect scores per player
I have a the following result from a SELECT query with ORDER BY player_id ASC, time ASC in PostgreSQL database: I’m trying to find each player’s longest streak where points = 100, with the tiebreaker being whichever streak began most recently. I also need to determine the time at which that player’s longest streak began. The expected result would be:
MySQL first_value on varchar column returns records in wrong format
first_value function in MySQL returns varchar column in unexpected format. I got a table ‘test’ with two columns create table test (col1 varchar(10), col2 integer); and has records like this, when …
What is wrong with this running total (cumulative column) in Oracle select with a window function?
I have a query containing 0 or 1 in a column. For demonstration purposes I replaced the 1 with 77 so its more easy to see: select dates.d the_date , case TO_CHAR(dates.d, ‘d’) when ‘7’ then 0 when ‘1’…
How to limit the no of results to just 5 per ‘user’ but allow for more rows if the immediate value at 5th row is same as the consecutive rows?
I have a database with Domain_id, salary and salary date of an organisation. My question is i have to find 5 highest salaries per domain, but the no of rows (5 rows) can increase to more, if the …
count(distinct) over (partition by… doesn’t work in Oracle SQL
I want to count the distinct day_number over the past 30 days. However, the distinct function can’t be used with over If I delete distinct, it will give me the total count of the day_number, but …
How to get repeatable partition as new one after another partition?
“Partition by” in OVER clause groups all of the values as unique, just like “Distinct” or “Group by” do. This is how it works in my query with row_number(): id st t row_number ————–…
How to add Conditions on SQL Percentile Window function?
I want to do a special query to evaluate team members. Each member will have a score, and those with score that is greater than 80th percentile of that team’s score will get a bonus. However, I want …
Select rows until running sum reaches specific value
I have the following data: DECLARE @t TABLE (usr VARCHAR(100), dt DATE, amount INT); INSERT INTO @t VALUES (‘a’, ‘2018-01-01’, 100), — 100 (‘a’, ‘2018-02-01’, 100), — 200 (‘a’, ‘2018-03-01’, 100), -…