From b8275dd1929c80147c62e0752f462d4ffc968620 Mon Sep 17 00:00:00 2001
From: Dan White <dan@whiteaudio.com>
Date: Fri, 23 Dec 2022 23:53:38 -0600
Subject: [PATCH] update analysis

---
 observations_analysis.sqbpro | 32 +++++++++++++++++++++-----------
 1 file changed, 21 insertions(+), 11 deletions(-)

diff --git a/observations_analysis.sqbpro b/observations_analysis.sqbpro
index f793538..77a9813 100644
--- a/observations_analysis.sqbpro
+++ b/observations_analysis.sqbpro
@@ -1,13 +1,4 @@
-<?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="times_index"/><default_encoding codec=""/><browse_table_settings><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 1">INSERT OR REPLACE INTO times_index
-	SELECT id, strftime(&quot;%s&quot;, start), strftime(&quot;%s&quot;, end)
-FROM observations
-WHERE strftime(&quot;%s&quot;, start) &lt;= strftime(&quot;%s&quot;, end);</sql><sql name="SQL 2">CREATE VIRTUAL TABLE times_index
-	USING rtree(id, start, end);</sql><sql name="SQL 3"></sql><sql name="SQL 4">select observer, count(vetted_status) from observations
-WHERE vetted_status = &quot;unknown&quot;
-GROUP BY observer
-ORDER BY observer;</sql><sql name="SQL 5">select ground_station, norad_cat_id, count(id) from observations
-GROUP BY ground_station, norad_cat_id
-ORDER BY ground_station, count(id);</sql><sql name="SQL 6">create index if not exists gs_norad
+<?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="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">create index if not exists gs_norad_index
 on observations(ground_station, norad_cat_id);
 
 create index if not exists gs_index
@@ -21,7 +12,26 @@ on observations(observer);
 
 create index if not exists observer_gs_index
 on observations(observer, ground_station);
-</sql><sql name="SQL 7">select observer, ground_station, count(id) from observations
+</sql><sql name="SQL 2">CREATE VIRTUAL TABLE IF NOT EXISTS times_index
+USING rtree(id, start, end);
+
+INSERT OR REPLACE INTO times_index
+SELECT id, strftime(&quot;%s&quot;, start), strftime(&quot;%s&quot;, end)
+FROM observations
+WHERE strftime(&quot;%s&quot;, start) &lt;= strftime(&quot;%s&quot;, end);
+</sql><sql name="SQL 4">select observer, count(vetted_status) from observations
+WHERE vetted_status = &quot;unknown&quot;
+GROUP BY observer
+ORDER BY observer;</sql><sql name="SQL 5">select ground_station, norad_cat_id, count(id) from observations
+GROUP BY ground_station, norad_cat_id
+ORDER BY ground_station, count(id);</sql><sql name="SQL 7">select observer, ground_station, count(id) from observations
 GROUP BY observer, ground_station
 ORDER BY observer, count(id) DESC, ground_station;
+</sql><sql name="SQL 3">select ground_station, observer, count(id) from observations
+GROUP BY ground_station, observer
+ORDER BY ground_station, count(id) DESC, observer;
+</sql><sql name="SQL 1">-- WIP: how many different observers have used each GS?
+select ground_station, count(*) from observations
+GROUP BY ground_station, observer
+ORDER BY count(distinct observer) DESC, ground_station;
 </sql><current_tab id="6"/></tab_sql></sqlb_project>
-- 
2.25.1