Identify the users with minimum votes and allocate new votes to that user in Synapse SQL

Avanish Tomar 20

Hello Experts I came up with one problem where I am looking for a solution as I have two tables:

Table1: Users

|UserID | TotalTasks|

| A3 | 12 |

| A4 | 14 |

| A5 | 11 |

Table2: Tasks

| TaskID | NewTask|

| 1 | 4 |

| 2 | 5 |

| 3 | 3 |

| 4 | 2 |

Output:

| 1 | 4 | A5 | 15 |

| 2 | 5 | A3 | 17 |

| 3 | 3 | A4 | 17 |

| 4 | 2 | A5 | 17 |

Here for table Tasks, first TaskID in order, I need to check which user has minimum TotalTasks from the users table and add that userID and NewTask + TotalTasks under the column "UpdatedTasks" and for the next TaskID, again need to check userID with updated minimum totalTasks and repeat thte smae process for each row without using loop as this I need to run in synapse sql. Any help much appriciated. thanks!

I tried with recursive cte but not getting right output

phemanth 12,740 Microsoft Vendor

@Avanish Tomar

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand the challenge you're facing. You want to allocate tasks to users based on their current task load, ensuring that each task is assigned to the user with the least number of tasks at that moment. Here's a solution using SQL in Azure Synapse without loops, leveraging window functions and common table expressions (CTEs):

-- Create the Users table
CREATE TABLE Users (
    UserID VARCHAR(2),
    TotalTasks INT
);

-- Insert data into Users table
INSERT INTO Users (UserID, TotalTasks) VALUES
('A3', 12),
('A4', 14),
('A5', 11);

-- Create the Tasks table
CREATE TABLE Tasks (
    TaskID INT,
    NewTask INT
);

-- Insert data into Tasks table
INSERT INTO Tasks (TaskID, NewTask) VALUES
(1, 4),
(2, 5),
(3, 3),
(4, 2);

-- Solution using CTEs and window functions
WITH TaskAssignment AS (
    SELECT 
        t.TaskID,
        t.NewTask,
        u.UserID,
        u.TotalTasks + t.NewTask AS UpdatedTasks,
        ROW_NUMBER() OVER (PARTITION BY t.TaskID ORDER BY u.TotalTasks) AS rn
    FROM 
        Tasks t
    CROSS JOIN 
        Users u
),
AssignedTasks AS (
    SELECT 
        TaskID,
        NewTask,
        UserID,
        UpdatedTasks
    FROM 
        TaskAssignment
    WHERE 
        rn = 1
),
UpdatedUsers AS (
    SELECT 
        u.UserID,
        u.TotalTasks + COALESCE(SUM(t.NewTask), 0) AS TotalTasks
    FROM 
        Users u
    LEFT JOIN 
        AssignedTasks t ON u.UserID = t.UserID
    GROUP BY 
        u.UserID, u.TotalTasks
)
SELECT 
    t.TaskID,
    t.NewTask,
    u.UserID,
    u.TotalTasks + t.NewTask AS UpdatedTasks
FROM 
    Tasks t
JOIN 
    UpdatedUsers u ON u.UserID = (
        SELECT 
            UserID
        FROM 
            UpdatedUsers
        ORDER BY 
            TotalTasks
        LIMIT 1
    )
ORDER BY 
    t.TaskID;

This script does the following:

Creates and populates the Users and Tasks tables.
Uses a CTE (TaskAssignment) to calculate the potential updated task count for each user for each task.
Uses another CTE (AssignedTasks) to select the user with the minimum current task count for each task.
Updates the Users table with the new task counts.
Finally, selects the tasks with their assigned users and updated task counts.

I hope the above steps will resolve the issue, please do let us know if issue persists. Thank you

Sina Salam 15,006

Hello Avanish Tomar,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

My understanding of your goal is that you would like to allocate tasks to users based on their current task load, and that each task is assigned to the user with the least number of tasks at that moment. Also, the desired output should be a table showing the task assignments and the updated task counts for each user.

To dynamically update the task counts for each user after each task assignment, and to make sure that the final output reflects the correct user assignments and updated task counts as required. I came up with the below:

-- Create the Users table
CREATE TABLE Users (
    UserID VARCHAR(2),
    TotalTasks INT
);
-- Insert data into Users table
INSERT INTO Users (UserID, TotalTasks) VALUES
('A3', 12),
('A4', 14),
('A5', 11);
-- Create the Tasks table
CREATE TABLE Tasks (
    TaskID INT,
    NewTask INT
);
-- Insert data into Tasks table
INSERT INTO Tasks (TaskID, NewTask) VALUES
(1, 4),
(2, 5),
(3, 3),
(4, 2);
-- Solution using a recursive CTE
WITH RECURSIVE TaskAssignment AS (
    -- Anchor member: Assign the first task
    SELECT 
        t.TaskID,
        t.NewTask,
        u.UserID,
        u.TotalTasks + t.NewTask AS UpdatedTasks,
        ROW_NUMBER() OVER (ORDER BY u.TotalTasks) AS rn
    FROM 
        Tasks t
    CROSS JOIN 
        Users u
    WHERE 
        t.TaskID = 1
    ORDER BY 
        u.TotalTasks
    LIMIT 1
    UNION ALL
    -- Recursive member: Assign subsequent tasks
    SELECT 
        t.TaskID,
        t.NewTask,
        u.UserID,
        u.TotalTasks + t.NewTask AS UpdatedTasks,
        ROW_NUMBER() OVER (ORDER BY u.TotalTasks) AS rn
    FROM 
        Tasks t
    JOIN 
        TaskAssignment ta ON t.TaskID = ta.TaskID + 1
    JOIN 
        Users u ON u.UserID = (
            SELECT 
                UserID
            FROM 
                Users
            ORDER BY 
                TotalTasks
            LIMIT 1
        )
)
SELECT 
    TaskID,
    NewTask,
    UserID,
    UpdatedTasks
FROM 
    TaskAssignment
ORDER BY 
    TaskID;

NOTE:

The first part of the recursive CTE assigns the first task to the user with the minimum TotalTasks.
The recursive part assigns each subsequent task to the user with the current minimum TotalTasks, updating the task counts dynamically.
The final SELECT statement retrieves the task assignments and updated task counts in the correct order.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.

Avanish Tomar 20 Reputation points

2024-12-29T10:28:17.0633333+00:00

thanks for quick solutition but I am facing issue while using your queries as Limit function is not working in Synapse.
Erland Sommarskog 114.8K Reputation points MVP

2024-12-29T13:45:31.6566667+00:00
It seems that both phemanth and Sina Salam violated the AI Usage Policy for Microsoft Q&A. Both Sina and phemanth should study this document in full before they post answers based on AI services again, but I like to mention two key points here:

You must explicitly give reference to the AI service you used.

You should validate the answer from the AI service.

You failed on both points! Shame on you!

The net result is that what you posted that were useless to the Avinash when they don't even run on Synapse! When it comes to pnemath's solution, it a simple change to make run on Synapse - replace the offending LIMIT 1 with a TOP 1. But alas, the result produced is incorrect!

Going back to the problem, I think it is very difficult to solve with a single statement, since the nature of the problem is iterative. And even more so, if we need the solution to scale. Avinash, how many users are there typically in the real world case? How many tasks? Also, are these tables in a dedicated pool or a serverless pool?

Finally, this seems to me as quite an unusual problem to run on an analytical platform. Not that would be that easier to solve on tradition SQL Server or Postgres.
Avanish Tomar 20 Reputation points

2024-12-30T08:31:36.15+00:00

@Erland Sommarskog Thanks for understanding my problem, I tried Top 1 in plcae of Limit but output was not the same what I am looking and as per your question, it is real time data where we can have n number of users and tasks, we can say its dynamic data and data is stored in Synapse view where we can't apply loop or temp table, that's why I am looking for a solution with the help of recursive cte. Thanks!
Erland Sommarskog 114.8K Reputation points MVP

2024-12-30T13:20:01.51+00:00

Hm, why can't you use loops or temp tables? I can't test right now, but I believe they are available in Synapse. Are you using a dedicated pool or a serverless pool?

I think the problem is very difficult - or impossible - to solve in a single statement. But maybe I haven't seen the light yet.

Real-time data in Synapse? I can help but thinking "is that really the correct design". Then again, that may be a decision beyond your control.
phemanth 12,740 Reputation points Microsoft Vendor

2024-12-30T18:30:58.98+00:00

@Avanish Tomar We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Erland Sommarskog 114.8K Reputation points MVP

2024-12-30T19:39:04.1066667+00:00

"Haven't heard of you"? Avinash posted as recently as 12 hours before your post. I think you should be a little more considerate before you post a nag message.

Accepted answer

Erland Sommarskog 114.8K MVP

Here is a solution that gives the expected results from your sample data. However, I am bending the rules! What I do is that I order the users on the current total number of tasks, and then I assign then tasks from the Tasks table in a round-robin fashion.

Depending on the business rules for your actual problem, this may be good enough - or it may be entirely inappropriate. But at least it is a relatively simple solution, and it is a single statement.

You never said whether you are using a serverless or dedicated pool, but this runs in the master database of my Synapse workspace, which I believe is the serverless pool.

CREATE TABLE #Users (
    UserID VARCHAR(2),
    TotalTasks INT
);
-- Insert data into Users table
INSERT INTO #Users (UserID, TotalTasks) VALUES
('A3', 12),
('A4', 14),
('A5', 11);
-- Create the Tasks table
CREATE TABLE #Tasks (
    TaskID INT,
    NewTask INT
);
-- Insert data into Tasks table
INSERT INTO #Tasks (TaskID, NewTask) VALUES
(1, 4),
(2, 5),
(3, 3),
(4, 2);

WITH UserNumbering AS (
   SELECT UserID, TotalTasks, 
          QueueOrder = row_number() OVER(ORDER BY TotalTasks),
          NoOfUsers = COUNT(*) OVER()
   FROM   #Users
), TaskNumbering AS (
   SELECT TaskID, NewTask,
          TaskNo = row_number() OVER (ORDER BY TaskID) - 1
   FROM   #Tasks
)
SELECT T.TaskID, T.NewTask, U.UserID,
       UpdatedTasks = U.TotalTasks + SUM(T.NewTask) OVER(PARTITION BY U.UserID
                                                         ORDER BY T.TaskNo 
                                                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM   TaskNumbering T
JOIN   UserNumbering U ON U.QueueOrder = T.TaskNo % U.NoOfUsers + 1
ORDER  BY T.TaskID
go
DROP TABLE #Tasks
DROP TABLE #Users

Avanish Tomar 20 Reputation points

2025-01-02T08:14:30.27+00:00

Thanks a lot!, this is working as per my expectation.

Share via

Identify the users with minimum votes and allocate new votes to that user in Synapse SQL

0 additional answers

Your answer