Clever Geek Handbook

Choose where the user buys each year

I have a table sales

that stores all sales. This table has columns of type year_ordered

, userId

, orderId

etc.

I want to write a SQL query to select rows where the user has ordered every year since 2008. So I only want those who have been loyal and ordered from 2008 to 2014.

I tried with this query, but it gave me anything where year_ordered

more than 2007.

select COUNT(*) as sales_count, ss.userID, ss.year_ordered 
from subscriber_sub ss
where ss.date_deleted is null
and ss.year_ordered > 2007
group by ss.year_ordered, ss.userID
having  COUNT(*) > 1 
order by ss.year_ordered
+3


source to share


5 answers


What you are aiming for is called relational division. There are two ways to do this:

select COUNT(distinct ss.year_ordered) as sales_count, ss.userID 
from subscriber_sub ss
where ss.date_deleted is null
  and ss.year_ordered > 2007
group by ss.userID
having  COUNT(distinct ss.year_ordered) >= ( select 2014 - 2008 )


Another way is to rewrite FORALL x: p (x) <=> NOT EXISTS x: NOT p (x), that is, users where it hasn't existed for a year, so no sales are sold this year. I'll leave this as an exercise :-)

+4


source


Try this for your suggestion HAVING

:



HAVING (SELECT COUNT(DISTINCT ss.year_ordered)) = 7
+2


source


this should list the subscriber entries of users who have purchased more than one item from 2007 to ...

select s2.* 
from subscriber_sub s2
where s2.year_ordered > 2007
and s2.userID in
(
    select ss.userID
    from subscriber_sub ss
    where ss.date_deleted is null
    and ss.year_ordered > 2007
    group by ss.userID
    having  COUNT(*) > 1 
)
+1


source


This should work dynamically i.e. keep working out the number of years the user should have done based on the current time and time:

DECLARE @subscriber_sub TABLE (
    userId INT,
    year_ordered INT,
    date_deleted DATE);
INSERT INTO @subscriber_sub VALUES (1, 2007, NULL);
INSERT INTO @subscriber_sub VALUES (1, 2008, NULL);
INSERT INTO @subscriber_sub VALUES (1, 2009, NULL);
INSERT INTO @subscriber_sub VALUES (1, 2010, NULL);
INSERT INTO @subscriber_sub VALUES (1, 2011, NULL);
INSERT INTO @subscriber_sub VALUES (1, 2012, NULL);
INSERT INTO @subscriber_sub VALUES (1, 2013, NULL);
INSERT INTO @subscriber_sub VALUES (1, 2014, NULL);
INSERT INTO @subscriber_sub VALUES (2, 2007, NULL);
INSERT INTO @subscriber_sub VALUES (2, 2008, NULL);
INSERT INTO @subscriber_sub VALUES (2, 2009, NULL);
INSERT INTO @subscriber_sub VALUES (2, 2010, NULL);
INSERT INTO @subscriber_sub VALUES (2, 2011, NULL);
INSERT INTO @subscriber_sub VALUES (2, 2012, NULL);
INSERT INTO @subscriber_sub VALUES (2, 2013, NULL);

WITH YearsOrdered AS (
    SELECT
        userId,
        COUNT(DISTINCT year_ordered) AS years
    FROM
        @subscriber_sub
    WHERE
        year_ordered > 2007
        AND date_deleted IS NULL
    GROUP BY
        userId)
SELECT 
    ss.userID,
    ss.year_ordered,
    COUNT(*) AS sales_count
FROM 
    @subscriber_sub ss
    LEFT JOIN YearsOrdered yo ON yo.userId = ss.userId
WHERE 
    ss.date_deleted IS NULL
    AND ss.year_ordered > 2007
    AND yo.years = DATEDIFF(YEAR, '20070101', GETDATE())
GROUP BY 
    ss.year_ordered,
    ss.userID
ORDER BY 
    ss.year_ordered,
    ss.userId;

When I tested User # 1, but User # 2 was not what he had not ordered in 2014.

+1


source


If you only need to know the users, you need to select userId

with counting different values year_ordered

= 7 (2008 to 2014 including):

select COUNT(*) as sales_count, ss.userID
from subscriber_sub ss
where ss.date_deleted is null
and ss.year_ordered > 2007
group by ss.userID
having  COUNT( DISTINCT ss.year_ordered) = 7
+1


source






More articles:


All Articles