Peeyush Gupta | 41212d6 | 2023-12-20 13:47:06 -0800 | [diff] [blame^] | 1 | /* |
| 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 | |
| 20 | drop dataverse test if exists; |
| 21 | create dataverse test; |
| 22 | |
| 23 | use test; |
| 24 | |
| 25 | create type dt1 as {Id:int}; |
| 26 | create dataset collection0(dt1) primary key Id; |
| 27 | |
| 28 | CREATE 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) |
| 42 | default NULL AS `collection0`; |
| 43 | |
| 44 | SELECT `Staples`.`Employee Name` AS `Employee Name`, |
| 45 | AVG(`Staples`.`Employee Salary`) AS `avg:Employee Salary:ok` |
| 46 | FROM `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`)) |
| 55 | GROUP BY `Staples`.`Employee Name`; |