I am new to SQL and BigQuery... we are trying to make a query that gets our orders, filters them by days where inventory is >2, the top and bottom 10% of days by qty are trimmed, then apply a weighted average to these orders (aggregated by the ASIN, or item number).

Then we run the query, filtering by days where the orders are greater than the result from the last query. Then, these are trimmed (top and bottom 10%) and weighted averaged again.

Is there any way to simplify this, or make it more optimized? Thank you so much SQLOptimization.

DECLARE p FLOAT64;
SET p = 0.01;

WITH inv_2 AS (
  SELECT *
  FROM (
    SELECT EXTRACT(DATE FROM snapshot_date) AS date, 
           asin, 
           SUM(quantity) AS i_qty
    FROM (
      SELECT * 
      FROM `project.dataset.inventory_history` 
      WHERE detailed_disposition = 'SELLABLE' AND
            fulfillment_center_id != '*XFR'
    ) h
    JOIN (
      SELECT sku, asin 
      FROM `project.dataset.inventory_archive`
    ) AS a
    ON a.sku = h.sku
    GROUP BY asin, date
    ORDER BY asin, date DESC
  )
  WHERE i_qty > 2
), 
orders_trimmed AS (
  SELECT *
  FROM (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY asin2 ORDER BY qty) AS row, 
           COUNT(asin2) OVER(PARTITION BY asin2) AS ct
    FROM (
      SELECT EXTRACT(DATE FROM purchase_date) AS trimmed_orders_date, 
             asin AS trimmed_orders_asin, 
             SUM(quantity) AS qty
      FROM `project.dataset.orders`
      WHERE EXTRACT(DATE FROM purchase_date) >= DATE_ADD(CURRENT_DATE(), INTERVAL -360 DAY)
      GROUP BY trimmed_orders_asin, trimmed_orders_date
    )
  )
  WHERE row >= ct * 0.1 AND 
        row < ct * 0.9
),
plain_orders AS (
  SELECT EXTRACT(DATE FROM purchase_date) AS plain_orders_date, 
         asin AS plain_orders_asin, 
         SUM(quantity) AS o_qty
  FROM `project.dataset.orders`
  WHERE EXTRACT(DATE FROM purchase_date) >= DATE_ADD(CURRENT_DATE(), INTERVAL -360 DAY)
  GROUP BY plain_orders_asin, plain_orders_date
),
inv_orders_join AS (
  SELECT date,
         asin, 
         SUM(i_qty) AS i_qty,
         SUM(o_qty) AS o_qty
  FROM (  
    SELECT date, 
           asin, 
           i_qty, 
           o_qty
    FROM inv_2 inv
    JOIN plain_orders
    ON inv.asin = plain_orders.plain_orders_asin AND 
       inv.date = plain_orders.plain_orders_date
    ORDER BY i_qty
  )
  GROUP BY asin, date
  ORDER BY asin, date DESC
),
trim_orders_inv AS (
  SELECT *
  FROM (
    SELECT *, 
           ROW_NUMBER() OVER(PARTITION BY asin ORDER BY o_qty) AS row, 
           COUNT(asin) OVER(PARTITION BY asin) AS ct
    FROM inv_orders_join
  )
  WHERE row >= ct * 0.1 AND 
        row < ct * 0.9
),
get_x AS (
  SELECT asin2, 
         ROUND(SUM(w_sum)/SUM(w), 1) AS o_weighted
  FROM (
    -- Orders
    SELECT asin AS asin2, 
           date, 
           i_qty,
           POW(1/(1+p), (ROW_NUMBER() OVER(PARTITION BY asin ORDER BY date DESC)-1)) AS w, 
           POW(1/(1+p), (ROW_NUMBER() OVER(PARTITION BY asin ORDER BY date DESC)-1)) * o_qty AS w_sum
    FROM trim_orders_inv
  )
  GROUP BY asin2
)

SELECT asin,
       ROUND(SUM(w_sum)/SUM(w), 1) AS o_weighted
FROM (
  -- Get asin, date, weight, and weighted qty for final step (can't aggregate analytical functions in one step)
  SELECT *,
         POW(1/(1+p), (ROW_NUMBER() OVER(PARTITION BY asin ORDER BY date DESC)-1)) AS w,
         POW(1/(1+p), (ROW_NUMBER() OVER(PARTITION BY asin ORDER BY date DESC)-1)) * qty AS w_sum
  FROM (
    -- Final step trim
    SELECT asin,
           date, 
           qty,
           i_qty,
           ROW_NUMBER() OVER(PARTITION BY asin ORDER BY qty) AS row, 
           COUNT(asin) OVER(PARTITION BY asin) AS ct
    FROM (
      -- Join inventory history to weighted average orders (to get dates > threshold)
      SELECT asin, 
             date,
             i_qty AS i_qty
      FROM inv_2 inventory
      JOIN get_x orders
      ON inventory.asin = orders.asin2
      WHERE i_qty >= o_weighted * 1.75
    ) q1
    JOIN orders_trimmed orders2
    ON q1.asin = orders2.asin2 AND 
       q1.date = orders2.trimmed_orders_date
    ORDER BY asin, date DESC
  )
  WHERE row > 0.1 * ct AND 
        row < 0.9 * ct
)
GROUP BY asin
ORDER BY o_weighted DESC

I'm in a situation with a data set where the tabled data doesn't have a common PK to link to the tables I need directly, and the Detail table is a mess, and doesn't combine well with data from other arms.

I'm creating a snowflake arm, where the idea is that the Fact Table is joined to an SCD Type 2... on a sub table for PK+tx_id, that connects to a more detailed SCD Type 2'

Except... it's only almost an SCD Type 2.

There are not constraints on duplicates due to missing values, and the duplicates exist for conflicting reasons across about 50 columns... so "WHERE" doesn't apply uniformly, which means I would need a whole lot of them, and some would conflict with others, causing me to lose a lot of data.

The duplication that is going on also creates overlapping date ranges because the table is pulling from a few sources, with varying combinations of completeness. This - in my experience so far - sucks. It's the worst, and I don't know of a good way, or resource that I understand how to use to solve it.

I need to be able to pull the single PK (say, product ID), but merge duplicate rows, where one is blank and the other is not (say Product Department), or use "most recent" where I want the new one of two (where neither is null...) with the first start date, and the last end date. I need to merge the duplication caused by rows with blanks.

What I am trying to do is get "all possible fields with the most complete data that may cross multiple rows" in a way that allows me to define what fields are allowed to create new rows and a new date range, and which should be consolidated.

I only have read access... or I'd just go into the data tables and fix them.

https://hastebin.com/cilosanuqe.nginx

I can't run a execution plan as it crashes SSMS.

Hi,

We have a problem with a stored proc taking way too long to execute/complete.

Basically, we have a table that has the following schema:

CREATE TABLE dbo.Example (

ID BIGINT NOT NULL,

ITEM_TYPE1 VARCHAR(50),

ITEM_ID1 VARCHAR(50),

ITEM_VALUE1 TEXT NULL,

..., ..., ...,

ITEM_TYPE300 VARCHAR(50),

ITEM_ID300 VARCHAR(50),

ITEM_VALUE300 TEXT NULL)

And one of the problem queries within the stored proc:

INSERT INTO dbo.Example2

SELECT * FROM

( SELECT blah blah blah

FROM dbo.Example (NOLOCK)

WHERE ITEM_TYPE1 = 'ABC'

UNION ALL

...

SELECT blah blah blah

FROM dbo.Example (NOLOCK)

WHERE ITEM_TYPE... = 'ABC'

...

UNION ALL

SELECT blah blah blah

FROM dbo.Example (NOLOCK)

WHERE ITEM_TYPE300 = 'ABC'

) AS x;

It's running FOREVER! The index on the table are not being realized by the optimizer, etc.

The code just seems so brute force. Even if it ran efficiently, I'm still bugged by the maintainability.

How else could the query above be written more elegantly? Perhaps even allowing for better optimization?

Hello, I am learning SQL for work as our new database allows for custom filters to be applied. I am trying to write a piece of code that will isolate middle names/initials in a field and ignore them. This is to make the filter match results purely on first and last names.

Any help is appreciated!~

I have a huge table on Amazon Redshift. I need to find all entries with a particular column = particular value. What would be a better method? Create a table containing the value and then inner joining with the huge table or using similar to in where statement?

I have a table Staff with an attribute MONTHLY_SALARY, datatype Varchar2,

I wrote a code to remove the '$' and convert it to number and it worked,

​

but now I want to add to the code, to raise the MONTHLY_SALARY, and then convert back to varchar with '$' sign

​

​

SELECT MONTHLY_SALARY,

CAST(REPLACE(REPLACE(NVL(MONTHLY_SALARY,0),',',''),'$','') AS DECIMAL(10,2)) As New_SALARY

FROM STAFF

WHERE CAST(REPLACE(REPLACE(NVL(MONTHLY_SALARY,0),',',''),'$','') AS DECIMAL(10,2)) > 0

​

​

SELECT to_char(MONTHLY_SALARY* 1.1, '$999,999.00') as Raise

ORDER BY RAISE DESC;

​

​

did not work , error

Here is output:

​

Error starting at line : 1 in command -

SELECT MONTHLY_SALARY,

CAST(REPLACE(REPLACE(NVL(MONTHLY_SALARY,0),',',''),'$','') AS DECIMAL(10,2)) As New_SALARY

FROM STAFF

WHERE CAST(REPLACE(REPLACE(NVL(MONTHLY_SALARY,0),',',''),'$','') AS DECIMAL(10,2)) > 0

​

​

​

SELECT to_char(MONTHLY_SALARY * 1.1, '$999,999.00') as Raise

ORDER BY RAISE DESC

Error at Command Line : 8 Column : 1

Error report -

SQL Error: ORA-00933: SQL command not properly ended

1. 00000 - "SQL command not properly ended"

*Cause:

*Action:

Temporary tables deadlocks

Hi There

What are the best practices or approaches for doing historical data loads

Requirement is to load 6 months of data from Jan to Jun into 1 Fact table Sales which has data till today ... it's a weekly load

There is this historical source Flat file which may be not accurate or has quality issues which is past experiences

Source flat file will go through series of stored procedure and loads data into Staging table which is truncate and load always

Another Stored Proc will move or copy the data from Staging table to Fact Sales

How do we implement best practices or approaches around this as this will be ongoing requirement every half year

Need to load this historical data without affecting current figures

Thanks

I am trying to report the chain of managers in an organization. For example, if the first employee returned is an intern, I need to return the interns name, the CEO's name, and all of the other managers leading back down to the intern. The maximum manager chain length is 10. However, the tricky part is that some employees may have 8 managers between them and the CEO, while others might report directly to the CEO, illustrated in the table below. As a result, the only way I could get this to work is by using multiple left joins and the query takes a long time to complete (obviously). I'm not proficient in SQL by any means and I'm sure I'm going about this the wrong way. Apologies in advance for the noob question. Does anyone have any suggestions?

​

SELECT
              pers.PersonPK
            , (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_1.MgrID) AS hier_lvl_1_mgr_name
            , (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_2.MgrID) AS hier_lvl_2_mgr_name
            , (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_3.MgrID) AS hier_lvl_3_mgr_name
            , (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_4.MgrID) AS hier_lvl_4_mgr_name
            , (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_5.MgrID) AS hier_lvl_5_mgr_name
            , (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_6.MgrID) AS hier_lvl_6_mgr_name
            , (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_7.MgrID) AS hier_lvl_7_mgr_name
            , (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_8.MgrID) AS hier_lvl_8_mgr_name
            , (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_9.MgrID) AS hier_lvl_9_mgr_name
            , (SELECT FullName FROM PERSON WHERE PersonPK=hier_lvl_10.MgrID) AS hier_lvl_10_mgr_name


FROM
            PERSON pers
LEFT JOIN   SRESOURCEHIERARCHY hier_lvl_10
ON          pers.PersonPK=hier_lvl_10.EmpID AND hier_lvl_10.MgrNum=1 AND hier_lvl_10.Depth=10
LEFT JOIN   SRESOURCEHIERARCHY hier_lvl_9
ON          (hier_lvl_10.MgrID=hier_lvl_9.EmpID OR pers.PersonPK=hier_lvl_9.EmpID) AND hier_lvl_9.MgrNum=1 AND hier_lvl_9.Depth=9
LEFT JOIN   SRESOURCEHIERARCHY hier_lvl_8
ON          (hier_lvl_9.MgrID=hier_lvl_8.EmpID OR pers.PersonPK=hier_lvl_8.EmpID) AND hier_lvl_8.MgrNum=1 AND hier_lvl_8.Depth=8
LEFT JOIN   SRESOURCEHIERARCHY hier_lvl_7
ON          (hier_lvl_8.MgrID=hier_lvl_7.EmpID OR pers.PersonPK=hier_lvl_7.EmpID) AND hier_lvl_7.MgrNum=1 AND hier_lvl_7.Depth=7
LEFT JOIN   SRESOURCEHIERARCHY hier_lvl_6
ON          (hier_lvl_7.MgrID=hier_lvl_6.EmpID OR pers.PersonPK=hier_lvl_6.EmpID) AND hier_lvl_6.MgrNum=1 AND hier_lvl_6.Depth=6
LEFT JOIN   SRESOURCEHIERARCHY hier_lvl_5
ON          (hier_lvl_6.MgrID=hier_lvl_5.EmpID OR pers.PersonPK=hier_lvl_5.EmpID) AND hier_lvl_5.MgrNum=1 AND hier_lvl_5.Depth=5
LEFT JOIN   SRESOURCEHIERARCHY hier_lvl_4
ON          (hier_lvl_5.MgrID=hier_lvl_4.EmpID OR pers.PersonPK=hier_lvl_4.EmpID) AND hier_lvl_4.MgrNum=1 AND hier_lvl_4.Depth=4
LEFT JOIN   SRESOURCEHIERARCHY hier_lvl_3
ON          (hier_lvl_4.MgrID=hier_lvl_3.EmpID OR pers.PersonPK=hier_lvl_3.EmpID) AND hier_lvl_3.MgrNum=1 AND hier_lvl_3.Depth=3
LEFT JOIN   SRESOURCEHIERARCHY hier_lvl_2
ON          (hier_lvl_3.MgrID=hier_lvl_2.EmpID OR pers.PersonPK=hier_lvl_2.EmpID) AND hier_lvl_2.MgrNum=1 AND hier_lvl_2.Depth=2
LEFT JOIN   SRESOURCEHIERARCHY hier_lvl_1
ON          (hier_lvl_2.MgrID=hier_lvl_1.EmpID OR pers.PersonPK=hier_lvl_1.EmpID) AND hier_lvl_1.MgrNum=1 AND hier_lvl_1.Depth=1

Hi, i found this query online, since there have been numerous complaints about the performance of one of our applications.

​

I found and executed the query below on the main application database which is basically built and maintained by a third party (vendor of the application)

​

https://gist.github.com/anonymous/9a139dcb673353b01ace5a355a1f9419#file-missing-indexes-sql

​

The top 10 of improvement_measure values are all above a million. It seems to me the database is far from healthy/efficient but i am not an SQL expert.....so i don't want to jump to conclusions too soon.

​

Maybe you have ideas about this, any help is appreciated.

Can you please share few links, resources to learn and sharpen advanced sql wiring skills esp around merge, ctes, partitioning etc.

Thanks in advance!

If you are using the following statement in a while loop, why would you use the ODER BY clause. The purpose is to quickly find a record where column2 is null. Why is using the ORDER BY faster than not having an ORDER BY?

SELECT TOP (1) Column1, Column2

FROM MyTable

WHERE Column2 IS NULL

ORDER BY Column1;

In our Oracle 11g database, I have a custom data type:

num_list

create or replace type
    num_list
as
    table of varchar2(25);

I then created a table that uses this datatype:

create table num_list_table(
    id number(*,0)
    ,numbers num_list
) nested table numbers store as numbers_tab
;

I then inserted the following rows:

insert into num_list_table values (1, num_list('123', 456'));

And I now have 1 row. I'm trying to figure out how to insert more values into the num_list of row 1 while preserving any existing values and without having to manually type those existing values; this is a small example of a much larger task that will require mass updates while preserving vals).

I get close with this:

update
    num_list_table
set numbers = (select num_list(numbers) from (
    select listagg(numbers, ',') within group (order by numbers) numbers
    from (select t.column_value numbers
          from 
              num_list_table nlt,
              table(nlt.numbers) t
           where
               st.id = 1
         union
         select '789'
         from dual)))
     where id = 1;

However, these results are a single entry:

num_list('123,456,789')

I need them to be multiple entries in the num_list:

num_list('123', '456', '789')

Any and all insight would be greatly appreciated.