blob: c21b569922e0fa4a765e76c02b1afdd2f75fa6fb [file] [log] [blame]
Peeyush Gupta41212d62023-12-20 13:47:06 -08001/*
2 * Licensed to the Apache Software Foundation (ASF) under one
3 * or more contributor license agreements. See the NOTICE file
4 * distributed with this work for additional information
5 * regarding copyright ownership. The ASF licenses this file
6 * to you under the Apache License, Version 2.0 (the
7 * "License"); you may not use this file except in compliance
8 * with the License. You may obtain a copy of the License at
9 *
10 * http://www.apache.org/licenses/LICENSE-2.0
11 *
12 * Unless required by applicable law or agreed to in writing,
13 * software distributed under the License is distributed on an
14 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
15 * KIND, either express or implied. See the License for the
16 * specific language governing permissions and limitations
17 * under the License.
18 */
19
20drop dataverse test if exists;
21create dataverse test;
22
23use test;
24
25create type dt1 as {Id:int};
26create dataset collection0(dt1) primary key Id;
27
28CREATE VIEW `Staples`(
29`Item Count` BIGINT NOT UNKNOWN, `Ship Priority` STRING NOT UNKNOWN, `Order Priority` STRING NOT UNKNOWN,
30`Order Status` STRING NOT UNKNOWN, `Order Quantity` DOUBLE NOT UNKNOWN, `Sales Total` DOUBLE NOT UNKNOWN,
31`Discount` DOUBLE NOT UNKNOWN, `Tax Rate` DOUBLE NOT UNKNOWN, `Ship Mode` STRING NOT UNKNOWN, `Fill Time` DOUBLE NOT UNKNOWN,
32`Gross Profit` DOUBLE NOT UNKNOWN, `Price` DOUBLE NOT UNKNOWN, `Ship Handle Cost` DOUBLE NOT UNKNOWN, `Employee Name` STRING NOT UNKNOWN,
33`Employee Dept` STRING NOT UNKNOWN, `Manager Name` STRING NOT UNKNOWN, `Employee Yrs Exp` DOUBLE NOT UNKNOWN, `Employee Salary` DOUBLE NOT UNKNOWN,
34`Customer Name` STRING NOT UNKNOWN, `Customer State` STRING NOT UNKNOWN, `Call Center Region` STRING NOT UNKNOWN, `Customer Balance` DOUBLE NOT UNKNOWN,
35`Customer Segment` STRING NOT UNKNOWN, `Prod Type1` STRING NOT UNKNOWN, `Prod Type2` STRING NOT UNKNOWN, `Prod Type3` STRING NOT UNKNOWN,
36`Prod Type4` STRING NOT UNKNOWN, `Product Name` STRING NOT UNKNOWN, `Product Container` STRING NOT UNKNOWN, `Ship Promo` STRING NOT UNKNOWN,
37`Supplier Name` STRING NOT UNKNOWN, `Supplier Balance` DOUBLE NOT UNKNOWN, `Supplier Region` STRING NOT UNKNOWN, `Supplier State` STRING NOT UNKNOWN,
38`Order ID` STRING NOT UNKNOWN, `Order Year` BIGINT NOT UNKNOWN, `Order Month` BIGINT NOT UNKNOWN, `Order Day` BIGINT NOT UNKNOWN,
39`Order Date` DATETIME NOT UNKNOWN, `Order Quarter` STRING NOT UNKNOWN, `Product Base Margin` DOUBLE NOT UNKNOWN, `Product ID` STRING NOT UNKNOWN,
40`Receive Time` DOUBLE NOT UNKNOWN, `Received Date` DATETIME NOT UNKNOWN, `Ship Date` DATETIME NOT UNKNOWN, `Ship Charge` DOUBLE NOT UNKNOWN,
41`Total Cycle Time` DOUBLE NOT UNKNOWN, `Product In Stock` STRING NOT UNKNOWN, `PID` BIGINT NOT UNKNOWN, `Market Segment` STRING NOT UNKNOWN)
42default NULL AS `collection0`;
43
44SELECT `Staples`.`Employee Name` AS `Employee Name`,
45 AVG(`Staples`.`Employee Salary`) AS `avg:Employee Salary:ok`
46FROM `Staples` `Staples`
47 INNER JOIN (
48 SELECT `Staples`.`Call Center Region` AS `Call Center Region`,
49 `Staples`.`Employee Name` AS `Employee Name`
50 FROM `Staples` `Staples`
51 GROUP BY `Staples`.`Call Center Region`,
52 `Staples`.`Employee Name`
53 HAVING ((AVG(`Staples`.`Employee Salary`)>=102499.99999999898) AND (AVG(`Staples`.`Employee Salary`)<=110000.00000000111))
54) `t0` ON ((`Staples`.`Call Center Region` = `t0`.`Call Center Region`) AND (`Staples`.`Employee Name` = `t0`.`Employee Name`))
55GROUP BY `Staples`.`Employee Name`;