1

i have a query that will create a calculated priority (c_priority) by store based on a primary priority column, the query works well to calculate c_priority using row_number() over (partition by store), however i need to go one step further and also sort this results by groups of 25 based on the campaign_column and priority and this is where i need help. I need the query to pull the first 25 records of the campaign with the highest priority, then pull the next 25 records of the following campaign and once it has finished with all campaigns gets back to the first campaign and pull another 25 records and cycle again until all records have a single sequence order (Calculated Priority) by store.

i will appreciate if someone can give an idea on how to achieve this, thanks!

current Query:

SELECT CONTACT_ID 
        ,LEAD_NUMBER
        ,LEAD_CREATION_DATE
        ,CUSTOMER_FIRST_NAME
        ,CUSTOMER_LAST_NAME 
        ,CAMPAIGN_NAME
        ,STORE
        ,MOBILE_NUMBER
        ,HOME_NUMBER
        ,WORK_NUMBER
        ,CELL_DO_NOT_CONTACT_FLAG
        ,HOME_DO_NOT_CONTACT_FLAG
        ,WORK_DO_NOT_CONTACT_FLAG
        ,PRIORITY
        ,ROW_NUMBER() OVER (PARTITION by store ORDER BY priority asc, campaign_name, store) AS     C_PRIORITY 
        ,HISTORICAL_DATE
        
FROM stores_campaigns_all

WHERE historical_date = '2023-03-10'
AND store IS NOT NULL

This is the current data output with my query: Current data output by my query

and this is how i want it to be, by rows of 2 to make it simple for this demostration how i need the data to be

0ldSch00lGm3r
  • 23
  • 4
  • Can you provide sample input and output data, perhaps with 3 rows in the cycle instead of 25 to keep the sample compact? – Greg Pavlik Mar 23 '23 at 00:57
  • Hi @GregPavlik i just edited my question and added the sample data, i made it simple using 2 rows pero cycle instead of 25 as i currently need it to be. Thanks! – 0ldSch00lGm3r Mar 23 '23 at 01:51

1 Answers1

1

This query creates groups of 2, and orders exactly as in your example data:

with data as (
    select value campaign, uniform(1, 1000, random()) random_data, dense_rank() over(order by campaign) priority
    from table(split_to_table('A A A B B B C C C C D D D D E E E E F F',' '))
), row_number_by_campaign as (
    select *, row_number() over(partition by campaign order by campaign) rn
    from data
), count_in_groups_of_2 as (
    select *, floor((rn-1)/2) group_in_campaign
    from row_number_by_campaign
)


select campaign, random_data, priority, row_number() over(order by group_in_campaign, campaign, rn) c_priority
from count_in_groups_of_2
order by group_in_campaign, campaign, rn

enter image description here

Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • One thing I don't understand... If it's in groups of 2, why are rows 13 and 14 not A and A? The intended output has A and B too, but when I first saw the sample data it didn't register why they're not A and A. – Greg Pavlik Mar 23 '23 at 16:57
  • 1
    I'd say because there are no more A's left at that time – Felipe Hoffa Mar 23 '23 at 19:47
  • Oh... That makes sense based on the output requirements. – Greg Pavlik Mar 23 '23 at 22:20
  • 1
    Hi @FelipeHoffa thanks for the help! this is exactly what i needed, i was able to adapt it to even do cycles by Store and worked perfectly. – 0ldSch00lGm3r Mar 23 '23 at 22:26