Joe Celko comes back with a puzzle that isn't new, but one where
the answer he originally gave now seems archaic: It is a deceptively simple
problem, but is it true that the new features of SQL have simplified the
solution? We leave it to the readers to provide the answer!
Oldies but Not So Goodies
There was a posting a SQL Server Newsgroup a short while
ago by a regular who was proud that his old T-SQL was still running today. He
saw it as a tribute to MS providing upward compatibility in their 4GL dialect
and his skill. Of course, there were problems when *= was deprecated, BIT
became a numeric type and a few other things happened to the SQL engine as it
moved from dialect to something closer to Standard SQL.
I appreciate the sentiment, but I would consider the fact
of my old T-SQL still running as a problem rather than a brag. In those days,
we used triggers where we now have DRI actions. We wrote a lot of IF-THEN-ELSE
logic where we now have CASE expressions. We nested subqueries where we now
have an OVER() clause. We truncated DATETIME to zero hour where we now have a
native DATE data type. The ISO-11179 naming conventions did not exist.
I cannot go back and modernize my old procedures. The
companies that were failures or got bought up are not around. The companies
that were successful have migrated code already – which is part of why they are
successful.
But I do have my old puzzle columns to haunt me. The
clever code of a decade or more ago might still work today, but it ought to be
replaced with a current release and rewritten with the new features. Let me
pull up one of these old puzzles and its old solution. I have cleaned up the
code just a little bit, so the data element names are better and you can cut &
paste it easily. If you have been around for awhile, you will recognize the old
design patterns we used when certain features were not available. Your task is
to write new answers with the current features available in SQL Server.
The Data Warehouse Problem
This one came in as a data warehouse problem in 1999. You
have a history table of customer daily total purchases that looks like this:
CREATE
TABLE
DailySalesTotals
(customer_id
CHAR(10)
NOT
NULL,
order_date DATE
NOT
NULL,
order_amt DECIMAL(8,2)
NOT
NULL,
PRIMARY
KEY (customer_id,
order_date));
Which we can fill with some dummy data.
INSERT
INTO
DailySalesTotals
(customer_id,
order_date,
order_amt)
VALUES ('Celko',
'1999-11-28',
450.00),
('Curly',
'1999-11-25',
400.00),
('Curly',
'1999-11-26',
300.00),
('Curly',
'1999-11-27',
400.00),
('Curly',
'1999-11-28',
450.00),
('Larry',
'1999-11-25',
400.00),
('Larry',
'1999-11-26',
400.00),
('Larry',
'1999-11-27',
450.00),
('Larry',
'1999-11-28',
400.00),
('Moe',
'1999-11-25',
400.00),
('Moe',
'1999-11-26',
400.00),
('Moe',
'1999-11-27',
400.00),
('Moe',
'1999-11-28',
400.00);
The problem is to report just those customers who
decreased their purchase amounts on their most recent order placed with us. We
are trying to get an idea when people are saturated with whatever we are
selling. If their order level is holding steady we are happy with them. I came
up with this query back then:
SELECT H1.customer_id,
' dropped purchase amount on ',
MAX(H1.order_date)
FROM
DailySalesTotals AS
H1
WHERE
H1.order_amt
<
(SELECT
H2.order_amt
FROM DailySalesTotals
AS H2
WHERE H1.customer_id
= H2.customer_id
AND H2.order_date
=
(SELECT
MAX(order_date)
FROM
DailySalesTotals AS
H3
WHERE H1.customer_id
= H3.customer_id
AND H1.order_date
> H3.order_date
)
)
AND
H1.order_date
=
(SELECT
MAX(
order_date)
FROM
DailySalesTotals h4
WHERE h4.customer_id
= H1.customer_id
)
GROUP
BY
customer_id;
The nested subquery says that the order amount has dropped
and then uses another subquery within itself to ask if the date is the most
recent date on file for that customer. Nesting correlated subqueries is usually
expensive, so we want to avoid that.
What is your answer in one SQL Statement?
I have included a script with some larger
volumes of test data sufficient to compare
the timings of both solutions.
The best answer to each stumper will be given a prize of a $100 Amazon voucher. The stumper will be run simultaneously on SQL Server Central and Simple-Talk. To see all the comments so far, you will need to visit both sites.
We will take entries for a week after the first Monday of publication, posted as comments to the articles.
Two weeks after the challenge is sent out, the judge's decision and comments will be sent out in the newsletter, and published on the site.
Joe Celko and Phil Factor will judge the answers to this puzzle. Your answer should :
1) Solve the problem -- Duh!
2) Avoid proprietary features in SQL Server that will not port or be good across
all releases, present and future.
3) Use Standard features in SQL Server that will be good across all releases,
present and future. Extra points for porting code.
4) Be clever but not obscure.
5) Explain how you got your answer.