3
\$\begingroup\$

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;
\$\endgroup\$
6
\$\begingroup\$

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;
\$\endgroup\$

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.