Finding users with the highest Following vs. Friends ratio

I am trying to find the users who have the highest "Following vs. Friends" ratio in my Twitter timeline.

I have a SQLite database of the users in my Twitter timeline and each column contains data found in the user section of the tweet JSON.

Is there a better way that I could write this search query?

Why does it return zero when the user has a higher friends count than followers count?

Is there a way to shorten this search query?

SELECT CAST(followers_count AS DECIMAL)/ CAST(friends_count AS DECIMAL), screen_name FROM following
ORDER BY CAST(
    CAST(followers_count AS DECIMAL)/ CAST(friends_count AS DECIMAL)
AS INT)
DESC LIMIT 25;

Your code is not working well, and the SQL statement is not formatted nicely either.

Reformatting your code in to logical sections produces.

SELECT CAST(followers_count AS DECIMAL)/ CAST(friends_count AS DECIMAL),
       screen_name
FROM following
ORDER BY CAST(CAST(followers_count AS DECIMAL)/ CAST(friends_count AS DECIMAL) AS INT) DESC
LIMIT 25;

SQLite is notorious for doing calculations based on the wrong affinity of the data values.

I immediately suspected that the SQL was doing integer division on the values even though you are casting them to decimal.

I put some examples together as an SQLFiddle here, and I cast the values to float instead, then used the SQLite round() function to get things decent. Doing floating-point division is what solves the zero-value division before.

This is the SQL I ended up with:

SELECT ROUND(CAST(followers_count AS float) / CAST(friends_count AS float), 2) as ratio,
       screen_name
FROM following
ORDER BY CAST(followers_count AS float) / CAST(friends_count AS float) DESC
LIMIT 25;

StackExchange Code Review Q#44000, answer score: 6