0

Write a SQL statement which can generate the list of customers whose minutes Streamed is consistently less than the previous minutes Streamed. As in minutes Streamed in the nth order is less than minutes Streamed in n-1th order, and the next previous order is also less. Another way to say it, list the customers that watch less and less minutes each time they watch a movie.

The table, query:

sqlfiddle link:

I have come up with the following query:

select distinct c1.customer_Id
from Customer c1
join Customer c2 
where c1.customer_Id = c2.customer_Id
  and c1.purchaseDate > c2.purchaseDate
  and c1.minutesStreamed < c2.minutesStreamed;

This query doesn't deal with the (n-1)st and (n-2)nd comparison, i.e. "minutes Streamed in the nth order is less than minutes Streamed in n-1th order, and the next previous order is also less." condition.

I have attached a link for sqlfiddle, where I have created the table.

jarlh
  • 42,561
  • 8
  • 45
  • 63
I-ILI
  • 23
  • 6

3 Answers3

0

Hello Continuous Learner,

the following statement works for the n-1 and n-2 relation.

select distinct c1.customer_Id 
from Customer c1 
join Customer c2 
on c1.customer_Id = c2.customer_Id
join Customer c3
on c1.customer_Id = c3.customer_Id
where c1.purchaseDate < c2.purchaseDate
and c1.minutesStreamed > c2.minutesStreamed
and c2.purchaseDate < c3.purchaseDate
and c2.minutesStreamed > c3.minutesStreamed

Although, I currently don't have an automatic solution for this problem.

Cheers

Kineolyan
  • 723
  • 8
  • 24
0

I would use a ROW_NUMBER() function with partition by customer id. and then do a self join, on customer id and rank = rank-1, to bring new and old at the same level

Like: create temp_rank_table as ( select customer_Id, purchaseDate , minutesStreamed, ROW_NUMBER() OVER (PARTITION BY customer_Id, ORDER BY purchaseDate, minutesStreamed) as cust_row from Customer )

self join

select customer_Id ( select newval.customer_Id, sum(case when newval.minutesStreamed < oldval.minutesStreamed then 1 else 0 end) as LessThanPrevCount, max(newval.cust_row) as totalStreamCount from temp_rank_table newval left join temp_rank_table oldval on newval.customer_id = oldval.customer_id and newval.cust_row-1 = oldval.cust_row -- cust_row 2 matches to cust_row 1 group by newval.customer_id )A where A.LessThanPrevCount = (A.totalStreamCount-1) -- get customers who always stream lesser than previous --you can use having clause instead of a subquery too

Ramya
  • 93
  • 1
  • 8
-1

DECLARE @TBL AS TABLE ( [NO] INT, [CODE] VARCHAR(50), [AREA] VARCHAR(50) )

/* EXAMPLE 1 */ INSERT INTO @TBL([NO],[CODE],[AREA]) VALUES (1,'001','A00') INSERT INTO @TBL([NO],[CODE],[AREA]) VALUES (2,'001','A00') INSERT INTO @TBL([NO],[CODE],[AREA]) VALUES (3,'001','B00') INSERT INTO @TBL([NO],[CODE],[AREA]) VALUES (4,'001','C00') INSERT INTO @TBL([NO],[CODE],[AREA]) VALUES (5,'001','C00') INSERT INTO @TBL([NO],[CODE],[AREA]) VALUES (6,'001','A00') INSERT INTO @TBL([NO],[CODE],[AREA]) VALUES (7,'001','A00')

/* EXAMPLE 2 / / ***** USE THIS CODE TO ENTER DATA FROM DIRECT TABLE ***** SELECT ROW_NUMBER() OVER(ORDER BY [FIELD_DATE]) AS [NO] ,[FIELD_CODE] AS [CODE] ,[FIELD_AREA] AS [AREA] FROM TABLE_A WHERE CAST([FIELD_DATE] AS DATE) >= CAST('20200307' AS DATE) ORDER BY [FIELD_DATE],[FIELD_CODE] */

SELECT A.NO AS ANO ,A.CODE AS ACODE ,A.AREA AS AAREA ,B.NO AS BNO ,B.CODE AS BCODE ,B.AREA AS BAREA ,CASE WHEN A.AREA=B.AREA THEN 'EQUAL' ELSE 'NOT EQUAL' END AS [COMPARE AREA] FROM @TBL A LEFT JOIN @TBL B ON A.NO=B.NO+1

Blockquote

Muhammad Ainul YBS
  • 9
  • 1