## Moving average: Calculate AVERAGE with the latest data N continuously

I have a table like this: Name Poin ================ ANA 5 ANA 10 ANA 3 ANA 8 ANA 7 ANA 5 I want to get AVG for last 2 rows continuosly like this: Name Poin AVG 2 ========================= ANA 5 7.5 ANA 10 6.5 ANA 3 5.5 ANA 8 7.5 ANA 7 6 ANA 5 5 The

## Corresponding column values ​​in the sequential SQL query

I have a table A col1 col2 1 81,123 2 90,99,100 3 4 And table 2 col1 col2 1 23 1 123 2 90 3 4 I want to join both the tables on first matching sequence for example : for col1 value =1 : 23 does not match neither with 81 or 123 so it would move to nex

## Snapshot of the row instance of the table with time intervals

I am not sure what to call this phenomenon. I have just titles it with "Snapshot of table row instance with time intervals". We have a situation where we need to create a new entity which is kind of JOIN of two tables but these two tables have h

## Ranking rows in a column under conditions on a different column

I have the following dataset: id | date | state ----------------------- 1 | 01/01/17 | high 1 | 02/01/17 | high 1 | 03/01/17 | high 1 | 04/01/17 | miss 1 | 05/01/17 | high 2 | 01/01/17 | miss 2 | 02/01/17 | high 2 | 03/01/17 | high 2 | 04/01/17 | mis

## Analytical counting on partition with and without ORDER BY clause

I don't understand why there are different results when using an ORDER BY clause in an analytic COUNT function. Using a simple example: with req as (select 1 as n, 'A' as cls from dual union select 2 as n, 'A' as cls from dual) select req.*, count(*)

## Add missing monthly dates in a series of time data in Postgresql

I have monthly time series data in table where dates are as a last day of month. Some of the dates are missing in the data. I want to insert those dates and put zero value for other attributes. Table is as follows: id report_date price 1 2015-01-31 4

## improve the query below row_number () over (partition by

Is there any way to improve query below, when I just check for one record it works faster(NAME='T 104') but when for all existing (113 records) records without NAME='T 104' it takes about 5 secs: the one below takes 73 msec: select name, PRODUCTION_D

## Monthly balance Postgres

I'm using postgreSQL 9.2 I have a table with transactions that affect an account startbalance and I want to create a query that generates the monthly balances. It seems simple, but I'm stuck and the closest thing I can find might be this Calculate Mo

## TSQL Row_Number

This question has been covered similarly before BUT I'm struggling. I need to find top N sales based on customer buying patterns.. ideally this needs to be top N by customer by Month Period by Year but for now i'm just looking at top N over the whole

## Select partitions based on matches in the other table

Having the following table (conversations): id | record_id | is_response | text | ---+------------+---------------+----------------------+ 1 | 1 | false | in text 1 | 2 | 1 | true | response text 3 | 3 | 1 | false | in text 2 | 4 | 1 | true | respons

## Total Excel working / window function

Say we have some data as followed Datetime Type Sale 25/07/2016 03:21 A 12 25/07/2016 04:00 B 23 25/07/2016 15:20 B 5 26/07/2016 05:15 A 15 26/07/2016 10:20 A 3 26/07/2016 16:10 B 20 27/07/2016 10:10 B 5 ....... Question is "Find the 36-hour frame wh

## Get first and last row using ROW_NUMBER and PARTITION BY

Sample Input Name | Value | Timestamp -----|-------|----------------- One | 1 | 2016-01-01 02:00 Two | 3 | 2016-01-01 03:00 One | 2 | 2016-01-02 02:00 Two | 4 | 2016-01-03 04:00 Desired Output Name | Value | EarliestTimestamp | LatestTimestamp -----|

## How can I group / window the events ordered by date delimited by an arbitrary expression?

I would like to group some data together based on dates and some (potentially arbitrary) indicator: Date | Ind ================ 2016-01-02 | 1 2016-01-03 | 5 2016-03-02 | 10 2016-03-05 | 15 2016-05-10 | 6 2016-05-11 | 2 I would like to group together

## rank () and over () in Oracle queries

I need to understand the difference between these two oracle queries specially between rank() over(order by length(cgp.group_name) desc) and rank() over(order by length(cgp.group_name), length(csc.subscriber_num) desc) ranking. I searched on google r

## How to use the LAG function in oracle pl sql until non-zero or the last row is reached?

I want to write LAG function for table column in such a way that if column value is zero it should lag value for that id if it is again zero for previous row it should LAG again until it reaches a non zero row or last row please help.Thanks in advanc

## MIN LAG in SQL Analytic

I have a table containing employees id, year id, client id, and the number of sales. For example: -------------------------------------- id_emp | id_year | sales | client id -------------------------------------- 4 | 1 | 14 | 1 4 | 1 | 10 | 2 4 | 2 |

## Select by the largest sum, but without the sum in the result

I need to select the top score of all combined attempts by a player and I need to use a WITH clause. create table scorecard( id integer primary key, player_name varchar(20)); create table scores( id integer references scorecard, attempt integer, scor

## add time from a timestamp field to another field

I'm trying to add the time (hours minutes and seconds) from the user initial date to the current date but I received an errors. I don't know what I do wrong, open to other suggestions select distinct uid, min (ts) over (partition by uid order by ts r

## Oracle: Get a value based on a previous record based on the period

due to security reason i deleting the table names.SorryYou need to specify to lag to handle each pgm_id separately, with a partition by clause: CREATE OR REPLACE FORCE VIEW HCRS.PGM_MEDI_LIVES_V ( PGM_ID, PERIOD_ID, MEDI_COV_LIVES, PREV_MEDI_COV_LIVE

## SQL Server: Using the Windows function with an ordered set

With this sample dataset: ID POS QTY A B 32 1 50 1 2 28 2 200 1 2 12 3 300 2 2 16 4 400 1 2 98 5 500 3 1 56 6 600 2 2 33 7 700 2 2 How can i get SUM(QTY) by group. Group is based on diferent (A AND B) ORDERED BY POS !!! As: ID POS QTY A B SUM(QTY) 32

## SQL mobile aggregation

Issue: For a given recordset containing a date column, an arbitrary number of category columns, and a value column, I would like to compute aggregations of value for an arbitrary date window, such as 30 days, 365 days, etc. I have looked at the windo

## SQL recursion with row_numbers

I got a temporary view in PostgreSQL which offers the following data (simplified): temporary_view (sum, rw): |sum | rw| |1337 | 1| |42 | 2| |... | ...| Now I need to do a recursion on this until my condition is reached. For simplicity, let's say I wa

## implement the line number ()

How can I implement row_number() window function in sql server without using the function itself, maybe with join on an index table? or more, how can I implement select ROW_NUMBER() OVER(PARTITION BY ATLAS_USER_ID ORDER BY Event_Time DESC) AS RR row

## How to sum the rows according to the partition in SQL?

how can you add values within rows according to it's partition and the sum will be placed below each partition (new row/s below each partition will be the container of the sum)? use the link below: http://sqlfiddle.com/#!3/4e3e80/4 column names: Prem

## Postgresql row_number not order by date

I am working on postgres 9.3.2 and I have this table: id startdate enddate no_of_days_between 1 2010-12-22 2010-12-23 1 1 2010-12-23 2010-12-24 1 1 2010-12-24 2010-12-25 1 1 2010-12-25 2010-12-26 1 1 2010-12-26 2010-12-27 1 1 2010-12-27 2010-12-28 1

## While I'm trying to use Partition over Hive,

SELECT prc.user_key, percentile(P.price, 0.5) OVER (PARTITION BY P.user_key) as median_price FROM ( SELECT vfc.user_key, vfc.order_id, MIN(vddo.cc) price FROM td_b.mv_a vfc JOIN td_b.dim_deal_option vddo ON vfc.d_key = vddo.d_key WHERE vfc.action = '

## How to calculate the mode for each value in another column in SQL

I have a table like this: ID Group Gender ------------------ 1 A M 2 A M 3 A F 4 A M 5 A U 6 B F 7 B F 8 B M 9 C U 10 C F 11 C U I am trying to calculate the mode group for each gender. In other words, for each gender, tell me which is the most popul

## PostgreSQL checks the element of a previous record

I need to check the previous record's element to make sure the date I query doesn't fall within a specific range between ending date and 7 days before starting date. I have the following code: create or replace function eight (date) returns text as \$

## PostgreSQL equivalent for TOP n with links: LIMIT & ldquo; with ties & rdquo;

I'm looking for something similar this in SQL Server: SELECT TOP n WITH TIES FROM tablename I know about LIMIT in PostgreSQL, but does the equivalent of the above exist? I'm just curious as it would save an extra query each time for me. If I have a t

## Selection of each Nth line per user in Postgres

I was using this SQL statement: SELECT "dateId", "userId", "Salary" FROM ( SELECT *, (row_number() OVER (ORDER BY "userId", "dateId"))%2 AS rn FROM user_table ) sa WHERE sa.rn=1 AND "userId" = 78