Ranter
Join devRant
Do all the things like
++ or -- rants, post your own rants, comment on others' rants and build your customized dev avatar
Sign Up
Pipeless API
From the creators of devRant, Pipeless lets you power real-time personalized recommendations and activity feeds using a simple API
Learn More
Comments
-
Wozza638786ySet 2 variables to the value of the highest reputation and the lowest down votes, then just select from where equal to those values
-
Akatana4326y@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
-
Akatana4326ySolved 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 :) -
inaba46316yWouldn'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 -
Akatana4326y@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.
-
inaba46316y@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
-
Akatana4326y@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 :) -
hexc11276yYou 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" -
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
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