I wondered how I could create a query to return rows where the base population is smaller than other countries , but the population_growth rate is at a percent fast enough that more people are added than the country with the larger population. Ideally I could have a condition where I return all those smaller countries which overtake the larger within 10 years.
I have just done complex queries in intermediate sql and thought I might be able to create two views, but do not know what of in this situation since I think I need to compare the population column to itself and also the growth_rate at the same time . I thought I might need UNION and the set operator EXCEPT but that is as far as I can get. Please advise?
Please specify what the end result is what you like. Would it be a table with two countries where one is able to overtake the other? Because otherwise it is a bit vague the term larger population.
What are you comparing with what? Do you mean China as the biggest country and you would like the amount of years it takes to overtake them?
One general advice on SQL queries is that you really want to have the end result clearly in your mind before writing a single line. It will save you 646 tries
By the way, well done trying this hard, I always just give up.
Would it be a table with two countries where one is able to overtake the other?
Correct.
The end result would be a column âyear1 predicted increaseâ ,âyear3 cumulative predicted increaseâ etc added to all the columns in the screenshot.
For example Indiaâs population after year1 and also Chinaâs.
Letâs assume Indiaâs yearly growth is 5% and China only 0.45% then we could see within under 5 years that India has overtaken China(I have not done calculation). So then there would be a final column population_after_5_years.
I amend orignal 10 as that would be too many columns.
Well, if you would like to create columns for every country for the next ten years that should not be too difficult. Just sum the population and the predicted increase every year. Donât forget to calculate the numbers with the new population though.
That was not quite it though because I wanted to return the set of countries whose population is larger than another country (so comparing within the population column) and the set where the population growth is larger but population smaller. Then performing some kind of SET operation, EXCEPT etc.
Otherwise just the sum of predicted population increase for all countries and not those that would be overtaken within 5 years, would be all countries in the table.
SELECT C.Name
FROM facts C
WHERE C.Population >= 100000
EXCEPT
SELECT C.Population
FROM facts C, facts L
WHERE C.Population <= L.Population
AND C.population_growth > L.population_growth;
I am sorry, this is too hard to follow for me. Youâre talking about countries, whereas you would like to compare two countries before. This is really confusing.
Sorry, I was not that clear.
the two countries were an example.
I meant a comparison of all countries who have populations smaller than another so that is one set.
the other set is that of population_growth.
Put all countries in set A1 who have smaller growth than those in A2
Blockquote
I meant a comparison of all countries who have populations smaller than another so that is one set.
the other set is that of populatin_growth.
Specify another you canât just use that vague term!
One last tip, try not to edit so many times after you post, itâs really difficult to react to you when you change your posts all the time
I would recommend, leave this question and go on with the lessons.
EXCEPT
If you get a very, very crystal clear image of what you want.
Can you please provide a sample input and a sample output (even if done âby handâ). I, too, am having trouble understanding what youâre trying to get.
I am thinking of creating two table one with populations in year one the other in year 5 then performing a join so I can see population in year1 and year5 next to each other along with the countries that meet the criteria in above screenshot.
Youâre getting that error because youâre running SQL code in Python, as if it were Python code. Can you please also share your code as text so that I can run it on my end?
%%sql
SELECT
t2.name,
t1.population/1000000,
t2.population_growth,
FROM facts.name as t1
JOIN facts.name as t2
ON t1.name = t2.name AND t1.population_growth > t2.population_growth
AND t1.population < t2.population;
I am trying just to get the population in year 5 column by adapting the compound interest formula into sql from this page , but it is not workingâ
That code snippet yields an error for me. I fixed it (I think) to the following:
SELECT t2.name, t1.population/1000000, t2.population_growth
FROM facts as t1
JOIN facts as t2
ON t1.name = t2.name AND t1.population_growth > t2.population_growth
AND t1.population < t2.population;
But this returns zero rows. Can you manually create input and output tables?
input would be above table filtered to the rows according to the criteria here
```
SELECT t2.name, t1.population/1000000, t2.population_growth
FROM facts as t1
JOIN facts as t2
ON t1.name = t2.name AND
t1.population_growth > t2.population_growth
AND t1.year1.population < t2.population
AND t1.year5.population >t2.population;
```
although I am not quite sure how to relate year1 column to year 5 such that I can show the rows which have been overtaken in population by those
rows which had a larger population and smaller population growth in year1
are filtered out if they have been overtaken in population terms by those which had a smaller population but higher growth.
I asked for input and output. You gave me code as input that doesnât run.
I realize (now) thatâs part of the problem, but that still means input is missing and that doesnât help matters. A visual representation of the table (like I showed in the example) would have probably worked better.
Anyway, what you want to do isnât possible in pure SQLite as SQLite doesnât implement a power function.
That a pure SQLite solution doesnât exist, isnât helpful to getting the results you see. So how is it that youâd get what you want? The most obvious solution is to run SQL from Python, maybe get the data as a dataframe and then use pandas.
An alternative is to leverage the power of mathematics. Exponentiation can be reduced (with an error margin) to a polynomial. This may work. I wonât write the details of this unless this is an avenue youâre interested in pursuing.
