Decode Maximum Number In Rows For Sql

I am using the #standardsql in bigquery and trying to code the maksimum ranking of each customer_id as 1, and the rest of it are 0 This is the query result so far The query for ra

Solution 1:

Based on your sample data, your ranking is unstable, because you have multiple rows with the same key values. In any case, you can still do what you want without subqueries, just using case:

select t.*,
       row_number() over (partitionby customer_id orderby booking_date asc) as ranking,
       (casewhenrow_number() over (partitionby customer_id orderby booking_date asc) =count(*) over (partitionby customer_id)
             then1else0end) as custom_coded
from t;

A more traditional way of doing essentially the same thing would be to use a descending sort:

select t.*,
       row_number() over (partitionby customer_id orderby booking_date asc) as ranking,
       (casewhenrow_number() over (partitionby customer_id orderby booking_date desc) =1then1else0end) as custom_coded
from t;

Solution 2:

We can wrap your current query, and then use MAX as an analytic function with a partition by customer to compare each ranking value against the max ranking for each customer. When the ranking value equals the maximum value for a customer, then we assign 1 for the custom_coded, otherwise we assign 0.

SELECT
    customer_id, item_bought, booking_date, ranking,
    CASEWHEN ranking =MAX(ranking) OVER (PARTITIONBY customer_id)
         THEN1ELSE0ENDAS custom_coded
FROM
(
    SELECT customer_id, item_bought, booking_date,
        ROW_NUMBER() OVER (PARTITIONBY customer_id ORDERBY booking_date) ranking
    FROM yourTable
) t;