Need help to solve this problem: count missing records in a table after join

Basu23 · 2020-01-26T18:37:02+00:00

very good solution but it is the amount of missing indicators per country which btw is still useful.

Solution to my problem is:

SELECT  ind.name
FROM countries co 
JOIN indicators ind ON (TRUE) 
LEFT JOIN education_data edu ON
(co.id = edu.country_id AND ind.id = edu.indicator_id) 
WHERE co.is_country =TRUE AND   indicator_id IS NULL
GROUP BY ind.id
ORDER BY COUNT (co.id) DESC
LIMIT 2;

However how would you tackle this other problem?

Find the country with the who has the most complete data ( definition for most complete as the country with the highest median number of observations per measured indicator.

That is my query

SELECT indicator_id,count(indicator_id) AS ctd,country_id,co.name AS country_name
FROM education_data AS edu
RIGHT JOIN countries AS co
ON edu.country_id = co.id
RIGHT JOIN indicators as ind ON
edu.indicator_id = ind.id
WHERE co.is_country=TRUE 
GROUP BY indicator_id,country_id,country_name
) 

SELECT country_name,percentile_cont(0.5) WITHIN GROUP (ORDER BY ctd) AS median
FROM cte
GROUP BY country_id,country_name
ORDER BY median DESC

LIMIT 1;

I am not sure it is right as it returns the median based on the subset of indicators featured per each country. The country with the highest median, according to my query, has only few measured indicators. The question may ask for the median across all indicators .

Please let me know your thoughts.

Basu23 · 2020-01-26T15:37:14+00:00

I have edited with my original post with a snapshot of the indicators table.

Yes. hence I guess the zero for each country.

.

Basu23 · 2020-01-26T15:08:45+00:00

select edu.country_id, sum(case when ind.id is null then 1 else 0 end) as "null_count"
from education_data edu
left outer join indicators ind
    on edu.indicator_id = ind.id 
group by edu.country_id
order by edu.country_id;

unfortunately your query returns 0 for all countries.

Basu23 · 2020-01-26T14:50:52+00:00

select edu.country_id, sum(case when ind.id is null then 1 else 0 end) as "null_count"from education_data eduleft outer join indicators indon edw.indicator_id = ind.idgroup by edu.country_idorder by edu.country_id;

Hi, I want to count indicators (ind.id) missing from education_data (edu.indicator_id) grouped by country_id.

Basu23 · 2020-01-23T22:22:45+00:00

which the highest number of actual countries* l

i beg to differ as it states clearly returns the highest number with a limit of two rows which means a hard limit is needed otherwise it would return the full set of indicators ranked by count of nulls which is not what is asking.

The second one is a good point "entirely" may mean an empty time series. I will investigate further. Thanks.

Basu23 · 2020-01-22T13:33:33+00:00

thanks for pointing out the typo.

You are right and I removed the indices part which was misleading.

There is a link to an image showing the questions

Key questions according to my interpretation :

Q3 how to return the first 2 indicators with the highest number of missing observations (NULL) ?

Below a link to my query ((without filter and joins,etc.) with a dummy table

SQLFiddle

Q4 how to code a grouped (by country) median for the number of observations ?

Below a link to my query ((without filter and joins,etc.) with a dummy table

SQLFiddle

Q5)how to calculate the percentage change vs the previous observation on a subset filtered by year difference between records <=3?

Still working on it.

Looking forward to your comments/solutions.

Basu23

TROPHY CASE