What are the Squirrels doing? SQL fun and games

Posted by

Finding interesting data to improve your SQL skills can be a nuisance. There are of course plenty of freely available data sets out there, but analysing mortgage repayments over a 10 year cycle is not my idea of fun data to play with. (Apologies to any home loan brokers etc out there ).🤣

But how about checking out squirrels?!?! Yes, New York actually gathers data on squirrels from time to time. Just head on over to the their site to see the data they collect, or just click here to download the CSV file of data on 3000 squirrels.

To get you up and running quickly, I’ve pre-created the DDL to access the data as an external table, plus a view to sit on top of that table to present the data in a cleaner fashion. A couple of things to note if you’re comparing it to the descriptions on the New York site:

  • Spaces in column names replaced with underscore
  • The “Date” column renamed to “SightingDate”
  • The “Lat/Long” column renamed to “Lat_Long”

Get the script below from my git repo and you’re good to go!

SQL> drop table squirrels purge;

Table dropped.

SQL> create table squirrels (
  2   X number
  3  ,Y number
  4  ,Unique_Squirrel_ID varchar2(20)
  5  ,Hectare varchar2(5)
  6  ,Shift varchar2(3)
  7  ,SightingDate varchar2(12)
  8  ,Hectare_Squirrel_Number number
  9  ,Age varchar2(10)
 10  ,Primary_Fur_Color varchar2(30)
 11  ,Highlight_Fur_Color varchar2(30)
 12  ,Combination_of_Primary_and_Highlight_Color varchar2(100)
 13  ,Color_notes varchar2(128)
 14  ,Location varchar2(20)
 15  ,Above_Ground_Sighter_Measurement varchar2(10)
 16  ,Specific_Location varchar2(100)
 17  ,Running varchar2(8)
 18  ,Chasing varchar2(8)
 19  ,Climbing varchar2(8)
 20  ,Eating varchar2(8)
 21  ,Foraging varchar2(8)
 22  ,Other_Activities varchar2(128)
 23  ,Kuks varchar2(8)
 24  ,Quaas varchar2(8)
 25  ,Moans varchar2(8)
 26  ,Tail_flags varchar2(8)
 27  ,Tail_twitches varchar2(8)
 28  ,Approaches varchar2(8)
 29  ,Indifferent varchar2(8)
 30  ,Runs_from varchar2(8)
 31  ,Other_Interactions varchar2(128)
 32  ,Lat_Long varchar2(90)
 33  )
 34  organization external (
 35    type oracle_loader
 36    default directory temp
 37    access parameters (
 38      records delimited by newline
 39      skip 1
 40      fields terminated by ',' optionally enclosed by '"'
 41      missing field values are null
 42      (
 43        x
 44        ,y
 45        ,unique_squirrel_id
 46        ,hectare
 47        ,shift
 48        ,sightingdate
 49        ,hectare_squirrel_number
 50        ,age
 51        ,primary_fur_color
 52        ,highlight_fur_color
 53        ,combination_of_primary_and_highlight_color
 54        ,color_notes
 55        ,location
 56        ,above_ground_sighter_measurement
 57        ,specific_location
 58        ,running
 59        ,chasing
 60        ,climbing
 61        ,eating
 62        ,foraging
 63        ,other_activities
 64        ,kuks
 65        ,quaas
 66        ,moans
 67        ,tail_flags
 68        ,tail_twitches
 69        ,approaches
 70        ,indifferent
 71        ,runs_from
 72        ,other_interactions
 73        ,lat_long
 74      )
 75    )
 76    location ('Squirrel_Data.csv')
 77  )
 78  reject limit unlimited;

Table created.

SQL> REM select * from squirrels;
SQL> create or replace view v_squirrels as
  2  select
  3        x
  4        ,y
  5        ,unique_squirrel_id
  6        ,hectare
  7        ,shift
  8        ,to_date(sightingdate,'MMDDYYYY') sightingdate
  9        ,hectare_squirrel_number
 10        ,age
 11        ,primary_fur_color
 12        ,highlight_fur_color
 13        --,combination_of_primary_and_highlight_color
 14        ,color_notes
 15        ,location
 16        ,case when lower(above_ground_sighter_measurement) != 'false' then to_number(above_ground_sighter_measurement) end above_ground_sighter_measurement
 17        ,specific_location
 18        ,case when running = 'false' then 'N' else 'Y' end running
 19        ,case when chasing = 'false' then 'N' else 'Y' end chasing
 20        ,case when climbing = 'false' then 'N' else 'Y' end climbing
 21        ,case when eating = 'false' then 'N' else 'Y' end eating
 22        ,case when foraging = 'false' then 'N' else 'Y' end foraging
 23        ,other_activities
 24        ,case when kuks = 'false' then 'N' else 'Y' end kuks
 25        ,case when quaas = 'false' then 'N' else 'Y' end quaas
 26        ,case when moans = 'false' then 'N' else 'Y' end moans
 27        ,case when tail_flags = 'false' then 'N' else 'Y' end tail_flags
 28        ,case when tail_twitches = 'false' then 'N' else 'Y' end tail_twitches
 29        ,case when approaches = 'false' then 'N' else 'Y' end approaches
 30        ,case when indifferent = 'false' then 'N' else 'Y' end indifferent
 31        ,case when runs_from = 'false' then 'N' else 'Y' end runs_from
 32        ,other_interactions
 33        ,sdo_geometry(2001,4326,sdo_point_type(y, x, NULL),NULL,NULL) lat_long
 34  from squirrels;

View created.

Now we are ready to ask the BIG questions about New York squirrels🤣

SQL> select primary_fur_color, count(*)
  2  from   v_squirrels
  3  group by primary_fur_color
  4  order by 2;

PRIMARY_FUR_COLOR                COUNT(*)
------------------------------ ----------
Black                                 103
Cinnamon                              391
Gray                                 2472

4 rows selected.


Happy exploring!

One comment

  1. Hi Connor,

    Just to continue the fun …
    Looks like these are indeed individual squirrels, however, their NAME is missing from the file …

    Why do I say “NAME” ?
    Many, many years ago, when we still used to travel with my parents to nice places,
    we “learned” that ALL the squirrels in the forest did react to the name “MARIANA” :):)
    People simply gathered at the edge of the forest, and called them by this “common” name …
    and, as by a “wonder-word”, all the squirrels came from the forest to pick up nuts and almonds
    directly from people’s hands … and, after a short “validation check for integrity”, ran away with the “capture”,
    probably to make their deposits for winter time :):)

    It’s amazing what wonderful creatures do (still) live on this Earth …
    while we, the “wise species” are busy destroying it every day 😦 😦

    Have fun and enjoy a nice weekend,
    Iudith Mentzel

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.