Count number of cases where visitor rank is higher on one page then on another-CodePudding

I want to count number fullvisitorID where rank in /page_y is higher then rank in page_x. So in this case result would be 1, only 111

fullvisitorID	rank	page
111	1	/page_x
111	2	/page_y
222	1	/page_x
222	2	/page_x
333	2	/page_x
333	1	/page_y

CodePudding user response：

Consider below approach

select count(*) from (
  select distinct fullvisitorID
  from your_table
  qualify max(if(page='/page_y',rank,null)) over win > max(if(page='/page_x',rank,null)) over win
  window win as (partition by fullvisitorID)
)

CodePudding user response：

for count you can use COUNT and GROUP BY

SELECT fullvisitorID, COUNT(fullvisitorID), Page FROM table t1
WHERE rank = (SELECT MAX(t2.rank) FROM table t2 WHERE t2.fullvisitorID = t1.fullvisitorID)
Group By fullvisitorID, Page

CodePudding user response：

You can apply a SELF JOIN between the two tables, by matching on the "fullvisitorID" field, then force

the first table to have "page_y" values
the second table to have "page_x" values
rank of the first table to have higher rank of the second table

SELECT *
FROM       tab t1
INNER JOIN tab t2
        ON t1.fullvisitorID = t2.fullvisitorID
       AND t1.page = '/page_y'
       AND t2.page = '/page_x'
       AND t1.rank > t2.rank

CodePudding user response：

Table separation approach:

DECLARE @t1 TABLE ( fullvisitorID INT, [rank] INTEGER,[page] VARCHAR (max)) --here where page = x
DECLARE @t2 TABLE ( fullvisitorID INT, [rank] INTEGER,[page] VARCHAR (max)) --here where page = y 
INSERT INTO @t1 SELECT * FROM @test t WHERE t.[page] LIKE '/page_x'
 INSERT INTO @t2 SELECT * FROM @test t WHERE t.[page] LIKE '/page_y'
          
SELECT COUNT(*) FROM @t1 INNER JOIN @t2 ON [@t1].fullvisitorID = [@t2].fullvisitorID WHERE [@t1].rank < [@t2].rank

CodePudding user response：

SELECT COUNTIF(page = '/page_y') cnt FROM (
  SELECT * FROM sample_table WHERE page IN ('/page_x', '/page_y')
 QUALIFY ROW_NUMBER() OVER (PARTITION BY fullvisitorID ORDER BY rank DESC) = 1
);