SQL : Choose three stock tickers which have complete data between 2012 and 2017 in the Quandl Wiki data set

I have a custom_calendar table like this:

[1] https://imgur.com/YLUix5y “custom calendar image”

And I have a stocks market table ( v_eod_quotes_2012_2017 ) which looks like this:

[2] https://imgur.com/a/Ff7Gkw1 “v_eod_quotes_2012_2017”

As I mention in the question, I need to find out which three stock market symbols has complete data i.e data for every day in between 2012 and 2017.

I have the below query which I’m unsure of, because it is only returning me one row which is wrong. I wanted to first correct this query and get some data so that I can choose three ticker symbols. can anybody please help me correcting this query.

SELECT ticker, count(*)::real/
       (SELECT COUNT(*) 
        FROM custom_calendar
        WHERE trading=1 AND
              date BETWEEN '2012-01-01' AND '2017-12-31'
       )::real as pct_complete
FROM v_eod_quotes_2012_2017
GROUP BY ticker
HAVING count(*)::real/(SELECT COUNT(*) FROM custom_calendar WHERE trading=1 AND date BETWEEN '2012-01-01' AND '2017-12-31')::real>=1.00
ORDER BY pct_complete DESC;

Output expected is more number of rows while I’m getting just one row for this query.

[3] https://imgur.com/a/Td7EeHd ” result”

Any help would be appreciated. Thanks

Answer

I’m not quite sure why you are getting even one row. Your problem is that there is no connection between the quotes and the custom_calendar, so you are not counting the range between the two dates.

I think this does what you want:

SELECT q.ticker, COUNT(*)
FROM v_eod_quotes_2012_2017 q JOIN
     custom_calendar cc
     ON q.date = cc.date
WHERE cc.trading = 1 AND
      cc.date BETWEEN '2012-01-01' AND '2017-12-31'
GROUP BY q.ticker
HAVING COUNT(*) = (SELECT COUNT(*)
                   FROM custom_calendar cc
                   WHERE cc.trading = 1 AND
                         cc.date BETWEEN '2012-01-01' AND '2017-12-31')
                  );

The ratio is not helpful, because it should always be 1.

EDIT:

If the above generates zero rows, it is because no rows match. Instead, get the most traded stocks using:

SELECT q.ticker, COUNT(*)
FROM v_eod_quotes_2012_2017 q JOIN
     custom_calendar cc
     ON q.date = cc.date
WHERE cc.trading = 1 AND
      cc.date BETWEEN '2012-01-01' AND '2017-12-31'
GROUP BY q.ticker
ORDER BY COUNT(*) DESC