Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to get the user who has the most advertisements in the last 30 days?

I have two tables in my database as below:

categories table:

Column Name Column Type
ID INT
Name TEXT
IDParent DOUBLE

and advertisements table:

Column Name Column Type
ID INT
IDUser INT
DateAdded DATETIME
IDCategory INT
Price DOUBLE

I have a task in my project as below:

The user ID of the user who has the most ads in the period of one month (thirty days) in the real estate consultant category (including Estate) for each advertiser.

my code is:

SELECT IDUser
FROM (
    SELECT a1.IDUser,
           COUNT(a2.ID) AS AdCount
    FROM advertisements a1
    INNER JOIN advertisements a2 ON a1.IDUser = a2.IDUser
    INNER JOIN categories c ON a2.IDCategory = c.ID
    WHERE c.Name LIKE '%Estate%'
    AND a2.DateAdded BETWEEN a1.DateAdded AND DATE_ADD(a1.DateAdded, INTERVAL 30 DAY)
    GROUP BY a1.IDUser, a1.DateAdded
) AS Sub
GROUP BY IDUser
ORDER BY MAX(AdCount) DESC
LIMIT 1;

my code problem:

I think my code has a problem: it doesn’t calculate for All thirty days

and
I also want to override the code and use window functions

can you help me to do this?

Tags:

3

Answers


  1. 0

    You can use below query,

    SELECT IDUser, max(AdCount) FROM
(SELECT a1.IDUser as IDUser, COUNT(a1.ID) AS AdCount
    FROM advertisements a1
    INNER JOIN categories c ON a2.IDCategory = c.ID
    WHERE c.Name LIKE '%Estate%'
    AND a1.DateAdded >= a1.DateAdded - INTERVAL 30 DAY
    GROUP BY a1.IDUser) T1;
    Login or Signup to reply.
  2. 0 
    `WITH UserAdCounts AS (
    SELECT IDUser,
           COUNT(*) AS AdCount,
           RANK() OVER (ORDER BY count(*) DESC) AS RankByAdCount
    FROM Advertisements
    WHERE IDCategory IN (SELECT ID FROM Categories WHERE Name LIKE '%Estate%')
          AND DateAdded BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE()
    GROUP BY IDUser
)
SELECT IDUser
FROM UserAdCounts
WHERE RankByAdCount = 1;`

    The above is the query that I have solved on a sample dataset using the window function. If my approach is wrong, please suggest what went wrong.

    Login or Signup to reply.
  3. 0

    SELECT IDUser, COUNT(*) AS Ad_Count
    FROM advertisements
    WHERE IDCategory IN (SELECT ID FROM categories WHERE Name LIKE ‘%Estate%’)
    AND DateAdded >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    GROUP BY IDUser
    ORDER BY Ad_Count DESC
    LIMIT 1;

    Demo: https://dbfiddle.uk/Ai_ZhJMg

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search