
<p><em>As COVID-19 continues to spread, healthcare groups and companies of all kinds are under pressure to provide care in the face of increasing demand. </em><strong><em>Healthy Data</em></strong><em> is your window into how data can help organizations address this crisis. </em></p>



<p>COVID-19 continues to be the major challenge for governments, health care agencies, and businesses all over the world. Many governments have instituted lockdowns, businesses have closed, and people everywhere have been practicing social distancing to slow the spread of the virus. In California, Governor Gavin Newsom ordered nearly 40 million Californians to stay at home following predictions that nearly 56% of the state’s residents could contract the virus in eight weeks without such an order. How are such predictions made? In this post, we’ll use SQL to predict the number of COVID-19 cases day by day. Let’s get started.&nbsp;</p>



<h2 class="wp-block-heading">The dataset&nbsp;</h2>



<p>Johns Hopkins University (JHU) maintains a <a href="https://github.com/CSSEGISandData/COVID-19" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">GitHub repository</a> of various datasets around COVID-19 and has made the data available for educational and academic research purposes. The daily COVID-19 report data is available in CSV form in that repository. This <a href="https://www.kaggle.com/sudalairajkumar/novel-corona-virus-2019-dataset" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">Kaggle</a> repository maintains an aggregated version of the JHU dataset. Let’s use the <a href="https://dtdocs.sisense.com/article/csv-upload" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">CSV upload feature</a> of Sisense for Cloud Data Teams to import this data into a table for analysis.&nbsp;</p>



<figure class="wp-block-image size-full fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/COVIDSQL1.jpg" alt="" class="wp-image-95117"/></figure>



<h2 class="wp-block-heading">Step 1: Preparing the data</h2>



<p>We are going to be predicting the number of cases just in the U.S. So let’s filter the data to the “US” region and group the data by the number of confirmed cases on a given day. Let’s also add a row number to this data, which will be used for some SQL analysis later. Finally, let’s use common table expressions (CTEs) to set this up.</p>



<pre class="wp-block-code"><code>with
  us_cases as (
    select
      TO_DATE(OBSERVATIONDATE) as OBSERVATION_DATE
      , sum(confirmed) as CONFIRMED_CASES
    from
      &#91;covid_19_2020_03_29]
    where
      COUNTRYREGION = 'US'
    group by
      OBSERVATION_DATE
  )
  , us_cases_with_row_nums as (
    select
      row_number() over(partition by 1 order by OBSERVATION_DATE) as row_number
      , *
    from
      us_cases
  )</code></pre>



<h2 class="wp-block-heading">Step 2: Calculating the pandemic growth factor</h2>



<p>The pandemic growth factor (PGF) between two consecutive days can be calculated by dividing the number of confirmed cases for the latter day by the number of confirmed cases for the former day.&nbsp;</p>



<p><em>PGF for Day(N) = Number of Cases on Day(N)/Number of Cases on Day(N-1)</em></p>



<p>For example, the number of confirmed cases for March 28 is 121,478, and the number of confirmed cases for March 27 is 101,657. This gives a PGF of 121,478/101,657 = 1.19 for March 28. By calculating the average PGF for the last few days, we are going to predict how many cases there will be in the future.&nbsp;</p>



<p>Let’s use another CTE to set up the calculation of average PGF. This can be done by self joining the CTE we set up in step 1. To perform this self join, we added a row number to the data in step 1. Let’s take the average growth factor for the last five days to predict the number of cases for the next 10 days. The 10-day value is configurable and is passed through a <a href="https://dtdocs.sisense.com/article/date-range-filters" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">date range filter</a>.&nbsp;</p>



<pre class="wp-block-code"><code>, average_growth_factor as (
    select
      avg(curr.CONFIRMED_CASES / prev.CONFIRMED_CASES) as average_growth_factor
    from
      us_cases_with_row_nums as curr
      join us_cases_with_row_nums as prev on
      curr.row_number = prev.row_number + 1
  where
    curr.OBSERVATION_DATE > DATEADD(day, -5, &#91;daterange_start])
    and curr.OBSERVATION_DATE &lt;= &#91;daterange_start]
  )</code></pre>



<h2 class="wp-block-heading">Step 3: Predicting the number of future cases</h2>



<p>Pandemics grow at an exponential scale. Now that we have the average pandemic growth factor, we can predict how many cases there will be N days from today.&nbsp;</p>



<p><em>Number of Cases After N Days = (Number of Cases Today) * (Pandemic Growth Factor)^N</em></p>



<p>So we first need the number of cases on the day from which we are going to begin predicting. Let’s call it starting cases and set it up using a CTE.&nbsp;</p>



<pre class="wp-block-code"><code>, starting_cases as (select
      CONFIRMED_CASES as STARTING_CASES
    from
      us_cases
    where
      OBSERVATION_DATE >&#91;daterange_start]
    order by OBSERVATION_DATE
    limit 1
)</code></pre>



<p>Now that we have the starting cases, we are ready to make some projections on the future number of cases. The SQL for it is below.&nbsp;</p>



<pre class="wp-block-code"><code>select
  OBSERVATION_DATE
  , CONFIRMED_CASES
  , row_number() over(partition by 1 order by OBSERVATION_DATE) as DAY_NUMBER
  , ROUND(STARTING_CASES * power(average_growth_factor, DAY_NUMBER -1), 0) as PROJECTED_CASES
  , average_growth_factor
  , STARTING_CASES
from
  us_cases
  join average_growth_factor
join starting_cases
where
&#91;OBSERVATION_DATE = daterange]</code></pre>



<p>This outputs the data in the format below.&nbsp;</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://lh5.googleusercontent.com/1V8_VfwxmtAtHYO4FtDKjNuWqzmmhf0UVrcUWGkWHl9yHZsXOC3T676etGgVCzHxY3gaIOTUvHs14Ooq1VZiSXPaELjgBM3uWkyfFpqwra6Z2fK1o8szAZQ87EvX1xhHAkRZUusn" alt=""/></figure>



<p>As you can see, by using the average growth factor from March 13 to March 18, we have predicted the number of cases from March 19 to March 28. Our model predicted about 117,000 cases on March 28. In reality, there were about 121,000 cases.&nbsp;</p>



<h2 class="wp-block-heading">Step 4: Creating visualizations</h2>



<p>Now that we have the data in a table format, it’s time to create some visualizations to see how the actual versus predicted number of cases performed throughout the time period. Let’s draw a line chart with the date on the X-axis and actual as well as projected cases on the Y-axis.&nbsp;</p>



<figure class="wp-block-image size-full fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/COVIDSQL21.jpg" alt="" class="wp-image-95129"/></figure>



<p>This results in a chart like the one below.</p>



<figure class="wp-block-image size-full fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/COVIDSQL3.jpg" alt="" class="wp-image-95135"/></figure>



<p>Applying the projection to more recent dates, we have projected 793,000 cases on April 19. In reality, there were 759,000 confirmed cases. This time the number of confirmed cases is less than the number of projected cases. This is a good sign that indicates that the average pandemic growth factor has been decreasing.&nbsp;</p>



<figure class="wp-block-image size-full fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/COVIDSQL4.jpg" alt="" class="wp-image-95141"/></figure>



<p>We will know that the pandemic is coming to an end when the growth factor becomes closer to one. Anything we can do to slow the spread of the pandemic—<a href="https://www.sisense.com/blog/performing-social-network-analysis-to-fight-the-spread-of-covid-19/">social distancing</a>, frequent handwashing, disinfecting frequently used items, and following the guidelines of the state and local authorities—can help bring the growth factor down.&nbsp;</p>



<h2 class="wp-block-heading">Summary</h2>



<p>Using a few lines of SQL, we have prepared COVID-19 data to be analyzed and from that data built a simple prediction of how many COVID-19 cases there are likely to be in the near future. This shows the power of tools in our hands that help us perform data analysis today. Sisense for Cloud Data Teams (previously Periscope Data) empowers data teams to quickly connect to cloud data sources, then explore and analyze data in a matter of minutes using SQL, <a aria-label="R, and Python (opens in a new tab)" rel="noreferrer noopener" href="https://www.sisense.com/blog/r-vs-python-whats-the-best-language-for-natural-language-processing/" target="_blank">R, and Python</a>.</p>



<figure class="wp-block-image size-large"><a href="https://www.sisense.com/blog/data-visualizations-in-python-and-r/" target="_blank" rel="noopener noreferrer"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/data-vis-python-r-blog-cta-770x250-1.jpg" alt="" class="wp-image-112937"/></a></figure>



<p><em>Govind Rajagopalan is a senior engineering manager at Sisense. He has over 15 years of experience across both engineering and management roles with diverse companies, domains, teams, and technologies. He is excited to teach, always helps his teammates build their skills, and loves improving his craft.</em>&nbsp;</p>


Predicting COVID-19 Pandemic Growth Using SQL

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article