Learn More
Resend Email
2
Akatana
6y

So I am struggling with a SQL Query for my Database lecture.

This is the Table Layout:
Users(id:integer, reputation:integer, display_name:string,
day:integer, month:integer, year:integer, location:string,
up_votes:integer, down_votes:integer, age:integer)

This is the task:
Show the set of users who have the highest reputation and the lowest down_votes
than any other user. HINT: there is no user that is better than all other users on each of the
criterion individually. Thus, you need a query that can eliminate users that are worse on both
criteria than some other user (in Economics your query will return what is known as the Pareto
Set).

I have looked up the Pareto Set but I am not really sure how to implement it into SQL.

So does any one of you know how to implement this or could anyone lead me into the right direction?

Help is very appreciated :)

question

homework

sql

help

university

lecture
Favorite
Ranter
Comments
  • 1
    6y
    Set 2 variables to the value of the highest reputation and the lowest down votes, then just select from where equal to those values
  • 0
    6y
    How would you implement it anywhere else?
  • 0
    6y
    @Wozza365 that doesn't work sadly because there is no user that has the highest reputation and the lowest downvotes. One user has the highest reputation and another user has the lowest number of downvotes. That's the tricky part of this task :D
  • 0
    6y
    @AndSoWeCode I am not really sure. It's my first contact with the pareto set.
  • 0
    6y
    Solved it 👍🏻 thanks for the help.
    Didn't read the exercise carefully enough and thought that I have to select the user that is closest to the criterias. But I can have multiple users.
    So I just created a query that selects the users with the highest reputation and united it with the query that selects the users with the lowest amount of downvotes :)
  • 0
    6y
    Wouldn't it also be possible to subtract downvotes from reputation and then ordering by that?

    As in:

    SELECT display_name, (reputation - down_votes) AS that_thing_to_order_by

    FROM "Users"

    WHERE whatever

    ORDER BY that_thing_to_order_by
  • 0
    6y
    @inaba then you would still have every user in the result. You are only supposed to show the users with the highest reputation and the lowest downvotes.
  • 1
    6y
    @Akatana I am not sure I follow. Or more specifically I am not sure how you turn that in to an amount. It seems like you did manage to do it and I wouldn't mind seeing how that looks like. I am a bit confused :v
  • 0
    6y
    @inaba
    SELECT display_name FROM Users
    WHERE reputation=(SELECT MAX(reputation) FROM Users)
    UNION
    SELECT display_name FROM Users
    WHERE down_votes=(SELECT MIN(down_votes) FROM Users)

    This query displays the Users with the highest reputation and the users with the lowest amount of downvotes :)
  • 1
    6y
    You could take the most downvotes, subtract the downvotes by that to essentially make the highest value the best, then you could add the reputation and order by the result?

    Something like (max_downvote - downvotes) + reputation = total "good value" order by that value if you only want the best users just select all the ones with the highest "good value"
  • 2
    6y
    I would suggest trying to go to stack overflow...🤷‍♂️
  • 0
    6y
    That doesn't really produce a good result, would it?
    It might just return the most controversial asshole with 100000 upvotes and 2 million downvotes, and a couple of users who joined 20 minutes ago and have a score of zero.
    Pareto is more like a length of the vector of all the measures of quality, but downvotes don't represent a positive quality so you could just subtract its square, but I'm not sure how much sense that makes.

    Usually in data analysis in business, this would be a good case of NPS, if you had a neutral vote count. But it doesn't account for popularity or notoriety.

    Just try upvotes^2 - downvotes^2
Related Rants
devRant © 2021 Hexical Labs LLC
Privacy Policy  |  Terms of Service
Add Comment