|
SQL> CREATE TABLE sales(
2 product_id NUMBER(6),
3 cid NUMBER,
4 time_id DATE,
5 sold NUMBER(3),
6 amount NUMBER(10,2),
7 cost NUMBER(10,2)
8 );
Table created.
SQL>
SQL> CREATE TABLE products(
2 product_id NUMBER(6),
3 name VARCHAR2(50),
4 subcategory VARCHAR2(50),
5 category VARCHAR2(50),
6 min_price NUMBER(8,2)
7 );
Table created.
SQL>
SQL>
SQL>
SQL> select b.subcategory, sum(a.sold),ntile(4) over (ORDER BY SUM(a.sold) desc) as quartile,
2 row_number () over (order by sum(sold) desc) as rownumber
3 from sales a, products b
4 where a.product_id = b.product_id
5 and to_char(a.time_id, 'yyyy-mm') = '2001-06'
6 group by b.subcategory;
no rows selected
SQL>
SQL>
SQL>
SQL> drop table sales;
Table dropped.
SQL>
SQL> drop table products;
Table dropped.
SQL>
|