From cc5095b162878999746d983162e3f02cc7b48e80 Mon Sep 17 00:00:00 2001
From: Dan White <dan@whiteaudio.com>
Date: Sun, 25 Dec 2022 22:43:24 -0600
Subject: [PATCH] update SQL analysis

---
 observations_analysis.sqbpro | 45 +++++++++++++++++++++---------------
 1 file changed, 26 insertions(+), 19 deletions(-)

diff --git a/observations_analysis.sqbpro b/observations_analysis.sqbpro
index 29e5ba6..9bfdcdf 100644
--- a/observations_analysis.sqbpro
+++ b/observations_analysis.sqbpro
@@ -1,21 +1,21 @@
-<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="observations.db" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><current_tab id="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="7736"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><current_table name="observations"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="observations" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index_node" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index_parent" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index_rowid" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table></browse_table_settings></tab_browse><tab_sql><sql name="SQL 6">-- Indexes make the world go 'round ... faster.
-create index if not exists gs_norad_index
-on observations(ground_station, norad_cat_id);
+<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="observations.db" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><current_tab id="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="6286"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><current_table name="observations"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="observations" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index_node" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index_parent" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index_rowid" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table></browse_table_settings></tab_browse><tab_sql><sql name="SQL 6">-- Indexes make the world go 'round ... faster.
+CREATE INDEX IF NOT EXISTS gs_norad_index
+ON observations(ground_station, norad_cat_id);
 
-create index if not exists gs_index
-on observations(ground_station);
+CREATE INDEX IF NOT EXISTS gs_index
+ON observations(ground_station);
 
-create index if not exists norad_index
-on observations(norad_cat_id);
+CREATE INDEX IF NOT EXISTS norad_index
+ON observations(norad_cat_id);
 
-create index if not exists observer_index
-on observations(observer);
+CREATE INDEX IF NOT EXISTS observer_index
+ON observations(observer);
 
-create index if not exists observer_gs_index
-on observations(observer, ground_station);
+CREATE INDEX IF NOT EXISTS observer_gs_index
+ON observations(observer, ground_station);
 
-create index if not exists gs_observer_index
-on observations(ground_station, observer);
+CREATE INDEX IF NOT EXISTS gs_observer_index
+ON observations(ground_station, observer);
 </sql><sql name="SQL 2">-- Special index for speeding up time range queries.
 CREATE VIRTUAL TABLE IF NOT EXISTS times_index
 USING rtree(id, start, end);
@@ -61,10 +61,18 @@ FROM observations
 WHERE vetted_status = &quot;unknown&quot;
 GROUP BY observer
 ORDER BY num_unknown;</sql><sql name="SQL 5">-- Which satellites are listened to by which GS?
-select ground_station, norad_cat_id, count(id) from observations
+SELECT
+	ground_station,
+	norad_cat_id,
+	count(id) AS num_obs
+FROM observations
 GROUP BY ground_station, norad_cat_id
 ORDER BY ground_station, count(id);</sql><sql name="SQL 7">-- Favorite ground stations used by observers
-select observer, ground_station, count(id) from observations
+SELECT
+	observer,
+	ground_station,
+	count(id) AS num_obs
+FROM observations
 GROUP BY observer, ground_station
 ORDER BY observer, count(id) DESC, ground_station;
 </sql><sql name="SQL 3">-- Observations on each ground station, by total number per observer
@@ -72,9 +80,8 @@ ORDER BY observer, count(id) DESC, ground_station;
 SELECT
 	ground_station,
 	observer,
-	count(id)
+	count(id) AS num_obs
 FROM observations
-WHERE ground_station = 2
 GROUP BY ground_station, observer
 ORDER BY ground_station, count(id) DESC, observer;
 </sql><sql name="SQL 1">-- How many different observers have scheduled on each GS?
@@ -89,8 +96,8 @@ ORDER BY ground_station;
 SELECT
 	observer,
 	count(distinct ground_station) AS num_gs,
-count(id) AS num_obs
+	count(id) AS num_obs
 FROM observations
 GROUP BY observer
 ORDER BY num_gs DESC;
-</sql><current_tab id="3"/></tab_sql></sqlb_project>
+</sql><current_tab id="5"/></tab_sql></sqlb_project>
-- 
2.25.1