-1

Here is the structure of my tables:

User

|--------|------------|
| id     | name       |
|--------|------------|
| 1      | Name1      |
| 2      | Name2      |
| 3      | Name3      |
|--------|------------|

Post

|--------|------------|-------------|--------------------|
| id     | content    | user_id     |   created_at       | 
|--------|------------|-------------|--------------------|
| 1      | Content1   |  1          |2020-01-17 14:03:31 |
| 2      | Content2   |  1          |2020-01-17 16:18:23 |
| 3      | Content3   |  2          |2020-01-17 16:29:13 | 
|--------|------------|-------------|--------------------|

Comment

|--------|------------|-------------|----------|---------------------|
| id     | comment    | user_id     | post_id  |   created_at        |
|--------|------------|-------------|----------|---------------------|
| 1      | Comment1   |  1          |   1      | 2020-01-20 18:29:19 |
| 2      | Comment2   |  1          |   1      | 2020-01-22 17:25:49 |
| 3      | Comment3   |  2          |   2      | 2020-01-28 11:37:59 | 
|--------|------------|-------------|----------|---------------------|

Vote

|--------|-------------|----------|-----------------------|
| id     |  user_id    | post_id  |    created_at         |
|--------|-------------|----------|-----------------------|
| 1      |   1         |   1      | 2020-01-20 15:08:55.0 |
| 2      |   1         |   2      | 2020-01-20 15:13:29   |
| 3      |   2         |   2      | 2020-01-20 15:13:32   |
|--------|-------------|----------|-----------------------|

I want to find the top score of 10 users by week, month, the year following to formula:

  1. A user creates 1 post will have 10 points.
  2. A user creates 1 comment in a post that will have 5 points.
  3. A user votes in a post that will have 2 points.

Could anyone help me with this, please!

Thank you so much.

  • In addition to what you had listed in your question, please also list the code you generated and show us where it fails. We are ready to extend help from that point. Instead, if you expect anyone to write the whole code for you, it will never help improving your knowledge. – Natarajan N Napoleon Feb 13 at 4:29
  • hint 1: treat the week, month, year as separate queries for a start. hint2: rather than join, UNION them in separate queries of the post,comment,vote score hint 3: group by user_id, week(created_at); hint 4: look carefully at the COUNT function.; hint 5: aggregating by top 10 has this pattern; hint 6: incrementally develop the query; hint 7; start with what the end result will look like – danblack Feb 13 at 4:48
  • yes, i got it. But actually, i have no idea :) – dacoten Feb 13 at 4:55
  • @danblack. Thanks for your instruction. – dacoten Feb 13 at 5:04
  • 1
    Stack Exchange is not a free coding service website. You are expected to post the code that you attempted. If it doesn't work, we can try to help you. – Eric Feb 13 at 17:25
-1
select  userId,UsersByWeek,SUM(Score) AS 'Score' FROM
    (
        SELECT  SUM(pCnt + cCnt+ vCnt) AS 'Score,userId,WEEK(createdOn) AS 'UsersByWeek',
                YEAR(createdOn) AS 'UsersByYear',MONTH(createdOn) AS 'UsersByMonth' 
            (
                SELECT  count(p.id)*10 as 'pCnt',u.id as userId,p.created_at as 'createdOn'
                    from  post p
                    left join  user u  ON u.id = p.user_id
                    union 
                 select  count(c.id)*5 as 'cCnt',u.id as userId,c.created_at as 'createdOn'
                    from  comment c
                    left join  user u  ON u.id = c.user_id
                    left join  post p  ON p.post_id = c.post_id
                    union 
                 select  count(v.id)*2 as 'vCnt' as u.id as userId,v.created_at as 'createdOn'
                    from  vote v
                    left join  user u  ON u.id = v.user_id
                    left join  post p  ON p.post_id = v.post_id
            )b
            GROUP BY  userId
            ORDER BY  Score DESC 
    )n
    GROUP BY  UsersByWeeK
    ORDER BY  Score desc
    LIMIT  10;
  • Start by using using sqlformat to make this readable. You use of NOW() suggest this week is the result being retrieved. I'm not sure that was asked. There's some concepts here that is probably useful to the final solution so thanks for the attempt on behalf of the OP. – danblack Feb 13 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.