Wednesday, June 4, 2014

Fun with SQL - Silver Pockets Full

Silver Pockets Full, send this message to your friends and in four days the money will surprise you. If you don't, well, a pox on your house. Or something like that. I didn't know what it was, I just saw this in my FB feed:



Back in November, I checked to see the frequency of having incremental numbers in the date, like 11/12/13 (my birthday) and 12/13/14 (kate's birthday). I don't want to hear how the rest of the world does their dates either, I know (I now write my dates like YYYY/MM/DD on everything, just so you know, that way I can sort it...or something).

Anyway, SQL to test out the claim of once every 823 years. Yay SQL.

OK, I'm not going to go into the steps necessary because I'm lazy (and I'm just lucky to be writing here), so here it is:
select *
from
  (
    select 
      to_char( d, 'yyyymm' ) year_month,
      count( case
               when to_char( d, 'fmDay' ) = 'Saturday' then 1
               else null
             end ) sats,
      count( case
               when to_char( d, 'fmDay' ) = 'Sunday' then 1
               else null
             end ) suns,
      count( case
               when to_char( d, 'fmDay' ) = 'Friday' then 1
               else null
             end ) fris
    from
      (
        select to_date( 20131231, 'yyyymmdd' ) + rownum d
        from dual
          connect by level <= 50000
      )
    group by 
      to_char( d, 'yyyymm' )
  )
where fris = 5
  and sats = 5
  and suns = 5
So over the next 50,000 days, this happens 138 times. I'm fairly certain that doesn't rise to the once every 823 years claim. But it's cool, maybe.
YEAR_MONTH       SATS       SUNS       FRIS
---------- ---------- ---------- ----------
201408              5          5          5 
201505              5          5          5 
201601              5          5          5 
201607              5          5          5 
201712              5          5          5 
128 more occurrences...
214607              5          5          5 
214712              5          5          5 
214803              5          5          5 
214908              5          5          5 
215005              5          5          5 

 138 rows selected 
I'm not the only dork that does this either, here's one in perl. I'm sure there are others, but again, I'm lazy.

3 comments:

Unknown said...

Awesome Chet. SQL is fun stuff.

Anonymous said...

If you check it for only august:

select y, count(*)
from ( select months_between( m, lag(m) over ( order by m ) ) / 12 y
from ( select m
from ( select add_months( to_date( '01012014', 'ddmmyyyy' ), rownum ) m
from dual connect by level <= 16400
)
where to_char( last_day( m ), 'dd' ) = '31'
and to_char( m, 'dy', 'NLS_DATE_LANGUAGE=english' ) = 'fri'
and to_char( m, 'mm' ) = '08' -- check only august
)
)
group by y

Y COUNT(*)
1
11 45
6 102
5 44
12 3

Anonymous said...

Just quickly glancing at this years calender, March had 5 Saturdays, 5 Sundays and 5 Mondays. How does that fit with the Silver Pockets Full, or does it have to be Friday Saturday Sunday?

Additionally just last month (May 2014) had 5 Thursdays, Fridays and Saturdays. It's really not an uncommon thing for any month with 31 days to have 5 occurrences of of 3 consecutive days of the week. In actuality its quite impossible not to.