def test_adding_duplicate_reference_does_not_join_more_queries(self): queries = (mock_dataset.query.widget(f.HighCharts().axis( f.HighCharts.LineSeries(mock_dataset.fields.votes))).dimension( timestamp_daily).reference( f.DayOverDay(mock_dataset.fields.timestamp), f.DayOverDay(mock_dataset.fields.timestamp), ).sql) self.assertEqual(2, len(queries)) with self.subTest("base query is same as without reference"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp" ' 'LIMIT 200000', str(queries[0]), ) with self.subTest( "reference query is same as base query with filter on reference dimension shifted" ): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp" ' 'LIMIT 200000', str(queries[1]), )
def test_adding_duplicate_reference_does_not_join_more_queries(self): queries = slicer.data \ .widget(f.HighCharts() .axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \ .dimension(slicer.dimensions.timestamp) \ .reference(f.DayOverDay(slicer.dimensions.timestamp), f.DayOverDay(slicer.dimensions.timestamp)) \ .queries self.assertEqual(2, len(queries)) with self.subTest('base query is same as without reference'): self.assertEqual('SELECT ' 'TRUNC("timestamp",\'DD\') "$d$timestamp",' 'SUM("votes") "$m$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$d$timestamp" ' 'ORDER BY "$d$timestamp"', str(queries[0])) with self.subTest('reference query is same as base query with filter on reference dimension shifted'): self.assertEqual('SELECT ' 'TRUNC(TIMESTAMPADD(\'day\',1,"timestamp"),\'DD\') "$d$timestamp",' 'SUM("votes") "$m$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$d$timestamp" ' 'ORDER BY "$d$timestamp"', str(queries[1]))
def test_use_same_nested_query_for_joining_references_with_same_period_and_dimension(self): queries = slicer.data \ .widget(f.HighCharts() .axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \ .dimension(slicer.dimensions.timestamp) \ .reference(f.DayOverDay(slicer.dimensions.timestamp), f.DayOverDay(slicer.dimensions.timestamp, delta=True), f.DayOverDay(slicer.dimensions.timestamp, delta_percent=True)) \ .queries self.assertEqual(2, len(queries)) with self.subTest('base query is same as without reference'): self.assertEqual('SELECT ' 'TRUNC("timestamp",\'DD\') "$d$timestamp",' 'SUM("votes") "$m$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$d$timestamp" ' 'ORDER BY "$d$timestamp"', str(queries[0])) with self.subTest('reference query is same as base query with filter on reference dimension shifted'): self.assertEqual('SELECT ' 'TRUNC(TIMESTAMPADD(\'day\',1,"timestamp"),\'DD\') "$d$timestamp",' 'SUM("votes") "$m$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$d$timestamp" ' 'ORDER BY "$d$timestamp"', str(queries[1]))
def test_use_same_nested_query_for_joining_references_with_same_period_and_dimension_with_different_periods( self, ): queries = ( mock_dataset.query.widget( f.HighCharts().axis(f.HighCharts.LineSeries(mock_dataset.fields.votes)) ) .dimension(timestamp_daily) .reference( f.DayOverDay(mock_dataset.fields.timestamp), f.DayOverDay(mock_dataset.fields.timestamp, delta=True), # also work with modified dimensions f.YearOverYear(timestamp_daily), f.YearOverYear(timestamp_daily, delta=True), ) .sql ) self.assertEqual(3, len(queries)) with self.subTest("base query is same as without reference"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp"', str(queries[0]), ) with self.subTest("second query for all DoD references"): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp"', str(queries[1]), ) with self.subTest("third query for all YoY references"): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD('week',52,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," 'SUM("votes") "$votes_yoy" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp"', str(queries[2]), )
def test_date_dim_with_annual_interval(self): queries = ( mock_dataset.query.widget( f.HighCharts().axis(f.HighCharts.LineSeries(mock_dataset.fields.votes)) ) .dimension(f.year(mock_dataset.fields.timestamp)) .reference(f.DayOverDay(mock_dataset.fields.timestamp)) .sql ) self.assertEqual(2, len(queries)) with self.subTest("base query is same as without reference"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'Y\') "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp"', str(queries[0]), ) with self.subTest( "reference query is same as base query with filter on reference dimension shifted" ): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'Y')),'Y') \"$timestamp\"," 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp"', str(queries[1]), )
def test_filters_on_other_dimensions_are_not_adapted(self): queries = slicer.data \ .widget(f.HighCharts() .axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \ .dimension(slicer.dimensions.timestamp) \ .reference(f.DayOverDay(slicer.dimensions.timestamp)) \ .filter(slicer.dimensions.timestamp .between(date(2018, 1, 1), date(2018, 1, 31))) \ .filter(slicer.dimensions.political_party .isin(['d'])) \ .queries self.assertEqual(2, len(queries)) with self.subTest('base query is same as without reference'): self.assertEqual('SELECT ' 'TRUNC("timestamp",\'DD\') "$d$timestamp",' 'SUM("votes") "$m$votes" ' 'FROM "politics"."politician" ' 'WHERE "timestamp" BETWEEN \'2018-01-01\' AND \'2018-01-31\' ' 'AND "political_party" IN (\'d\') ' 'GROUP BY "$d$timestamp" ' 'ORDER BY "$d$timestamp"', str(queries[0])) with self.subTest('reference query is same as base query with filter on reference dimension shifted'): self.assertEqual('SELECT ' 'TRUNC(TIMESTAMPADD(\'day\',1,"timestamp"),\'DD\') "$d$timestamp",' 'SUM("votes") "$m$votes_dod" ' 'FROM "politics"."politician" ' 'WHERE TIMESTAMPADD(\'day\',1,"timestamp") BETWEEN \'2018-01-01\' AND \'2018-01-31\' ' 'AND "political_party" IN (\'d\') ' 'GROUP BY "$d$timestamp" ' 'ORDER BY "$d$timestamp"', str(queries[1]))
def test_single_reference_dod_with_dimension_but_not_reference_dimension_in_query_using_filter(self): queries = slicer.data \ .widget(f.HighCharts() .axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \ .dimension(slicer.dimensions.political_party) \ .reference(f.DayOverDay(slicer.dimensions.timestamp)) \ .filter(slicer.dimensions.timestamp.between(date(2000, 1, 1), date(2000, 3, 1))) \ .queries self.assertEqual(2, len(queries)) with self.subTest('base query is same as without reference'): self.assertEqual('SELECT ' '"political_party" "$d$political_party",' 'SUM("votes") "$m$votes" ' 'FROM "politics"."politician" ' 'WHERE "timestamp" BETWEEN \'2000-01-01\' AND \'2000-03-01\' ' 'GROUP BY "$d$political_party" ' 'ORDER BY "$d$political_party"', str(queries[0])) with self.subTest('reference query is same as base query with filter on reference dimension shifted'): self.assertEqual('SELECT ' '"political_party" "$d$political_party",' 'SUM("votes") "$m$votes_dod" ' 'FROM "politics"."politician" ' 'WHERE TIMESTAMPADD(\'day\',1,"timestamp") BETWEEN \'2000-01-01\' AND \'2000-03-01\' ' 'GROUP BY "$d$political_party" ' 'ORDER BY "$d$political_party"', str(queries[1]))
def test_reference_on_dimension_with_weekly_interval(self): weekly_timestamp = slicer.dimensions.timestamp(f.weekly) queries = slicer.data \ .widget(f.HighCharts() .axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \ .dimension(weekly_timestamp) \ .reference(f.DayOverDay(weekly_timestamp)) \ .queries self.assertEqual(2, len(queries)) with self.subTest('base query is same as without reference'): self.assertEqual('SELECT ' 'TRUNC("timestamp",\'IW\') "$d$timestamp",' 'SUM("votes") "$m$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$d$timestamp" ' 'ORDER BY "$d$timestamp"', str(queries[0])) with self.subTest('reference query is same as base query with filter on reference dimension shifted'): self.assertEqual('SELECT ' 'TRUNC(TIMESTAMPADD(\'day\',1,"timestamp"),\'IW\') "$d$timestamp",' 'SUM("votes") "$m$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$d$timestamp" ' 'ORDER BY "$d$timestamp"', str(queries[1]))
def test_build_query_with_totals_cat_dimension_with_references(self): queries = slicer.data \ .widget(f.DataTablesJS(slicer.metrics.votes)) \ .dimension(slicer.dimensions.timestamp, slicer.dimensions.political_party.rollup()) \ .reference(f.DayOverDay(slicer.dimensions.timestamp)) \ .queries self.assertEqual(len(queries), 4) with self.subTest( 'base query is same as without references or totals'): self.assertEqual( 'SELECT ' 'TRUNC("timestamp",\'DD\') "$d$timestamp",' '"political_party" "$d$political_party",' 'SUM("votes") "$m$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$d$timestamp","$d$political_party" ' 'ORDER BY "$d$timestamp","$d$political_party"', str(queries[0])) with self.subTest('reference query is shifted'): self.assertEqual( 'SELECT ' 'TRUNC(TIMESTAMPADD(\'day\',1,"timestamp"),\'DD\') "$d$timestamp",' '"political_party" "$d$political_party",' 'SUM("votes") "$m$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$d$timestamp","$d$political_party" ' 'ORDER BY "$d$timestamp","$d$political_party"', str(queries[1])) with self.subTest( 'base totals query is same as base query minus the totals dimension' ): self.assertEqual( 'SELECT ' 'TRUNC("timestamp",\'DD\') "$d$timestamp",' 'NULL "$d$political_party",' 'SUM("votes") "$m$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$d$timestamp" ' 'ORDER BY "$d$timestamp","$d$political_party"', str(queries[2])) with self.subTest( 'reference total query is shifted without the totals dimension' ): self.assertEqual( 'SELECT ' 'TRUNC(TIMESTAMPADD(\'day\',1,"timestamp"),\'DD\') "$d$timestamp",' 'NULL "$d$political_party",' 'SUM("votes") "$m$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$d$timestamp" ' 'ORDER BY "$d$timestamp","$d$political_party"', str(queries[3]))
def test_use_same_nested_query_for_joining_references_with_same_period_and_dimension( self, ): queries = ( mock_dataset.query.widget( f.HighCharts().axis(f.HighCharts.LineSeries(mock_dataset.fields.votes)) ) .dimension(timestamp_daily) .reference( f.DayOverDay(mock_dataset.fields.timestamp), f.DayOverDay(mock_dataset.fields.timestamp, delta=True), f.DayOverDay(mock_dataset.fields.timestamp, delta_percent=True), ) .sql ) self.assertEqual(2, len(queries)) with self.subTest("base query is same as without reference"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp"', str(queries[0]), ) with self.subTest( "reference query is same as base query with filter on reference dimension shifted" ): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp"', str(queries[1]), )
def test_dimension_with_multiple_references(self): queries = ( mock_dataset.query.widget( f.HighCharts().axis(f.HighCharts.LineSeries(mock_dataset.fields.votes)) ) .dimension(timestamp_daily) .reference(f.DayOverDay(mock_dataset.fields.timestamp)) .reference( f.YearOverYear(mock_dataset.fields.timestamp, delta_percent=True) ) .sql ) self.assertEqual(3, len(queries)) with self.subTest("base query is same as without reference"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp"', str(queries[0]), ) with self.subTest( "reference query is same as base query with filter on reference dimension shifted" ): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp"', str(queries[1]), ) with self.subTest( "reference query is same as base query with filter on reference dimension shifted" ): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD('week',52,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," 'SUM("votes") "$votes_yoy" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp"', str(queries[2]), )
def test_filters_on_other_dimensions_are_not_adapted(self): queries = ( mock_dataset.query.widget( f.HighCharts().axis(f.HighCharts.LineSeries(mock_dataset.fields.votes)) ) .dimension(timestamp_daily) .reference(f.DayOverDay(mock_dataset.fields.timestamp)) .filter( mock_dataset.fields.timestamp.between( date(2018, 1, 1), date(2018, 1, 31) ) ) .filter(mock_dataset.fields.political_party.isin(["d"])) .sql ) self.assertEqual(2, len(queries)) with self.subTest("base query is same as without reference"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2018-01-31' " "AND \"political_party\" IN ('d') " 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp"', str(queries[0]), ) with self.subTest( "reference query is same as base query with filter on reference dimension shifted" ): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN TIMESTAMPADD('day',-1,'2018-01-01') AND TIMESTAMPADD('day',-1,'2018-01-31') " "AND \"political_party\" IN ('d') " 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp"', str(queries[1]), )
def test_single_reference_dod_with_no_dimension_uses_multiple_from_clauses_instead_of_joins(self): queries = slicer.data \ .widget(f.HighCharts() .axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \ .reference(f.DayOverDay(slicer.dimensions.timestamp)) \ .queries self.assertEqual(2, len(queries)) with self.subTest('base query is same as without reference'): self.assertEqual('SELECT ' 'SUM("votes") "$m$votes" ' 'FROM "politics"."politician"', str(queries[0])) with self.subTest('reference query is same as base query'): self.assertEqual('SELECT ' 'SUM("votes") "$m$votes_dod" ' 'FROM "politics"."politician"', str(queries[1]))
def test_reference_without_selecting_ref_dimension_using_date_range_filter(self): queries = ( mock_dataset.query.widget( f.HighCharts().axis(f.HighCharts.LineSeries(mock_dataset.fields.votes)) ) .dimension(mock_dataset.fields.political_party) .reference(f.DayOverDay(mock_dataset.fields.timestamp)) .filter( mock_dataset.fields.timestamp.between( date(2000, 1, 1), date(2000, 3, 1) ) ) .sql ) self.assertEqual(2, len(queries)) with self.subTest("base query is same as without reference"): self.assertEqual( "SELECT " '"political_party" "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN '2000-01-01' AND '2000-03-01' " 'GROUP BY "$political_party" ' 'ORDER BY "$political_party"', str(queries[0]), ) with self.subTest( "reference query is same as base query with filter on reference dimension shifted" ): self.assertEqual( "SELECT " '"political_party" "$political_party",' 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN TIMESTAMPADD('day',-1,'2000-01-01') " "AND TIMESTAMPADD('day',-1,'2000-03-01') " 'GROUP BY "$political_party" ' 'ORDER BY "$political_party"', str(queries[1]), )
def test_reference_with_no_dimensions_or_filters_creates_same_query(self): # TODO reduce this to a single query queries = (mock_dataset.query.widget(f.HighCharts().axis( f.HighCharts.LineSeries(mock_dataset.fields.votes))).reference( f.DayOverDay(mock_dataset.fields.timestamp)).sql) self.assertEqual(2, len(queries)) with self.subTest("base query is same as without reference"): self.assertEqual( 'SELECT SUM("votes") "$votes" FROM "politics"."politician" ORDER BY 1 LIMIT 200000', str(queries[0]), ) with self.subTest("reference query is same as base query"): self.assertEqual( 'SELECT SUM("votes") "$votes_dod" FROM "politics"."politician" ORDER BY 1 LIMIT 200000', str(queries[1]), )
def test_reference_filters_are_applied(self, mock_fetch: Mock, mock_2: Mock, mock_apply_reference_filters: Mock): db = TestDatabase() t0 = Table("test0") dataset = DataSet( table=t0, database=db, fields=[ Field( "timestamp", label="Timestamp", definition=t0.timestamp, data_type=DataType.date, ), Field( "metric0", label="Metric0", definition=t0.metric, data_type=DataType.number, ), ], ) mock_widget = f.Widget(dataset.fields.metric0) mock_widget.transform = Mock() reference_filter = ReferenceFilter( dataset.fields.metric0, ComparisonOperator.gt, 5 ) reference = f.DayOverDay(dataset.fields.timestamp, filters=[reference_filter]) df = pd.DataFrame.from_dict({"$value": [1]}) mock_fetch.return_value = 100, df mock_apply_reference_filters.return_value = df ( dataset.query() .dimension(dataset.fields.timestamp) .widget(mock_widget) .reference(reference) ).fetch() mock_apply_reference_filters.assert_called_once_with(df, reference)
def test_build_query_with_rollup_cat_dimension_with_references_and_date_filters( self, ): queries = (mock_dataset.query().widget( f.ReactTable(mock_dataset.fields.votes)).dimension( f.day(mock_dataset.fields.timestamp)).dimension( f.Rollup(mock_dataset.fields.political_party)).reference( f.DayOverDay(mock_dataset.fields.timestamp)).filter( mock_dataset.fields.timestamp.between( date(2018, 1, 1), date(2019, 1, 1))).sql) self.assertEqual(len(queries), 4) with self.subTest( "base query is same as without references or rollup"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"political_party" "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2019-01-01' " 'GROUP BY "$timestamp","$political_party" ' 'ORDER BY "$timestamp","$political_party" ' 'LIMIT 200000', str(queries[0]), ) with self.subTest("reference query is shifted"): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD(day,1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," '"political_party" "$political_party",' 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN TIMESTAMPADD(day,-1,'2018-01-01') " "AND TIMESTAMPADD(day,-1,'2019-01-01') " 'GROUP BY "$timestamp","$political_party" ' 'ORDER BY "$timestamp","$political_party" ' 'LIMIT 200000', str(queries[1]), ) with self.subTest( "base rollup query is same as base query minus the rollup dimension" ): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' "'_FIREANT_ROLLUP_VALUE_' \"$political_party\"," 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2019-01-01' " 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$political_party" ' 'LIMIT 200000', str(queries[2]), ) with self.subTest( "reference total query is shifted without the rollup dimension" ): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD(day,1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," "'_FIREANT_ROLLUP_VALUE_' \"$political_party\"," 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN TIMESTAMPADD(day,-1,'2018-01-01') " "AND TIMESTAMPADD(day,-1,'2019-01-01') " 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$political_party" ' 'LIMIT 200000', str(queries[3]), )
def test_build_query_with_rollup_dimension_and_a_reference(self): queries = ( mock_dataset.query() .widget(f.ReactTable(mock_dataset.fields.votes)) .dimension( f.day(mock_dataset.fields.timestamp), f.Rollup(mock_dataset.fields.political_party), ) .reference(f.DayOverDay(mock_dataset.fields.timestamp)) .sql ) self.assertEqual(len(queries), 4) with self.subTest("base query is same as without references or rollup"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"political_party" "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$political_party" ' 'ORDER BY "$timestamp","$political_party"', str(queries[0]), ) with self.subTest("reference query is shifted"): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," '"political_party" "$political_party",' 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$political_party" ' 'ORDER BY "$timestamp","$political_party"', str(queries[1]), ) with self.subTest( "base rollup query is same as base query minus the rollup dimension" ): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'NULL "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$political_party"', str(queries[2]), ) with self.subTest( "reference total query is shifted without the rollup dimension" ): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," 'NULL "$political_party",' 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$political_party"', str(queries[3]), )