asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q98/q98.3.query.sqlpp

/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions AND limitations
 * under the License.
 */


USE tpcds;

SELECT
       currpricetable.i_item_desc,
       currpricetable.i_category,
       currpricetable.i_class,
       currpricetable.i_current_price,
       currpricetable.itemrevenue,
       (currpricetable.itemrevenue * 100 / revrattable.revrat) revenueratio
FROM
(SELECT i_item_desc,
        i_category,
        i_class,
        i_current_price,
        i_item_id,
        SUM(ss_ext_sales_price) itemrevenue
FROM
  store_sales,
  item,
  date_dim
WHERE
  ss_item_sk = i_item_sk
  AND i_category IN ["Jewelry", "Sports", "Books"]
  AND ss_sold_date_sk = d_date_sk
  AND date(d_date) >= date('2001-01-12')
  AND date(d_date) <= date('2001-02-11')
GROUP BY
  i_item_id,
  i_item_desc,
  i_category,
  i_class,
  i_current_price
) AS currpricetable
JOIN
(SELECT i_item_id, i_item_desc, i_category, i_class, SUM(ss_ext_sales_price) revrat
  FROM
  store_sales,
  item
  WHERE ss_item_sk = i_item_sk
  GROUP BY
     i_item_id,
     i_item_desc,
     i_category,
     i_class
) AS revrattable
ON  currpricetable.i_item_id = revrattable.i_item_id
AND currpricetable.i_class = revrattable.i_class
AND currpricetable.i_item_desc = revrattable.i_item_desc
AND currpricetable.i_category = revrattable.i_category
ORDER BY
  currpricetable.i_category,
  currpricetable.i_class,
  currpricetable.i_item_id,
  currpricetable.i_item_desc,
  revenueratio;