SQL for Data Scientists by Renee M Teate

Author:Renee M Teate [Renée M. P. Teate]
Language: eng
Format: epub
ISBN: 9781119669395
Publisher: Wiley
Published: 2021-08-30T00:00:00+00:00

Figure 9.9

Exploring Changes Over Time

One thought that comes to mind looking at the dates during this exploration is that we should check to find out if there is an indicator in the database showing whether the market changed format, perhaps allowing online orders during the COVID-19 pandemic in 2020, since that might have impacted sales, and that indicator could be valuable for predictive modeling.

Another question we might ask the database after seeing the output in Figure 9.9 is: Do most vendors sell at the market year-round, or is there a certain time of year when there are different numbers of vendors at the farmer’s market? We'll extract the month and year from each market date and look at the counts of vendors that appear in the data each month:

SELECT EXTRACT(YEAR FROM market_date) AS market_year, EXTRACT(MONTH FROM market_date) AS market_month, COUNT(DISTINCT vendor_id) AS vendors_with_inventory FROM farmers_market5.vendor_inventory GROUP BY EXTRACT(YEAR FROM market_date), EXTRACT(MONTH FROM market_date) ORDER BY EXTRACT(YEAR FROM market_date), EXTRACT(MONTH FROM market_date)

Since only three vendors have inventory entered into this example database, there isn't much variation seen in this output, but you can see in Figure 9.10 that there are three vendors in June through September, and two vendors per month the rest of the year. So one of the vendors (likely vendor 4, from the date ranges we saw in Figure 9.9) may be a seasonal vendor.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.