donaldpminer

October 21st, 2018 from EDHREC data

SELECT * FROM
(SELECT coloridentity, cmc, card, cnt, RANK() OVER (PARTITION BY (coloridentity, cmc) ORDER BY cnt DESC) rank
FROM
(SELECT card, aa.coloridentity as coloridentity, cmc, cnt FROM
  (SELECT ccid.card as card, coloridentity, COUNT(*) as cnt FROM (SELECT card, coloridentity FROM cards JOIN (SELECT * FROM decks WHERE savedate>='2017-10-20') decks ON cards.url=decks.url) ccid
    GROUP BY ccid.card, coloridentity) aa JOIN cardref ON aa.card=cardref.name) bb
) cc WHERE rank <= 3
ORDER BY coloridentity, cmc, rank

donaldpminer

Braden Bowdish
@bowdish_braden 

@edhrec Which card has had the most amount of money spent on it? Assuming
no one proxies or owns 1 copy and puts it in each deck.
price * number of decks it is in = ??

-----

Prices are from October 12th, 2018 Cardkingdom prices.

donaldpminer

SELECT commander, COUNT(*), COUNT(*)::float / (SELECT COUNT(*) FROM decks WHERE decks.commander=aa.commander) as perc FROM (SELECT commander, card FROM (SELECT * FROM cards WHERE card='Sol Ring') cards JOIN decks on cards.url=decks.url) aa GROUP BY commander ORDER BY perc;

commander            | count |       perc        
---------------------------------+-------+-------------------
 Budoka Gardener                 |     5 |             0.125
 Kodama of the North Tree        |     1 |             0.125
 Marhault Elsdragon              |     2 |             0.125
 Zurgo Bellstriker               |    48 | 0.185328185328185
 Kasimir the Lone Wolf           |     1 |               0.2
 Jerrard of the Closed Fist      |     4 | 0.210526315789474

donaldpminer

From EDHREC data, April 21st, 2018

SELECT to_char, AVG(numleg) FROM (SELECT decks.url, numleg, to_char(savedate, 'YYYY-MM') FROM (SELECT aa.url, COUNT(*) as numleg FROM (SELECT * FROM cards JOIN cardref ON cards.card = cardref.name WHERE cardref.type LIKE 'Legendary Creature%') aa GROUP BY url) bb JOIN decks ON bb.url=decks.url) cc GROUP BY to_char ORDER BY to_char;

 Month   | Average number of Legendary Creatures
 2014-09 | 3.5555555555555556
 2014-10 | 3.7694524495677233
 2014-11 | 3.5817575083426029
 2014-12 | 3.7218193803559657
 2015-01 | 3.6833881578947368

donaldpminer

SELECT a.card, a.count as lastmonthcount, b.count as weekcount, b.count::float / ( SELECT COUNT(*) FROM view_cards_week ) - a.count::float / (SELECT COUNT(*) FROM view_cards_lastmonth) as diff FROM (SELECT card, COUNT(*) FROM view_cards_lastmonth GROUP BY card) a JOIN (SELECT card, COUNT(*) FROM view_cards_week GROUP BY card) b ON a.card=b.card ORDER BY diff DESC LIMIT 100;

           card            | lastmonthcount | weekcount |         diff         
---------------------------+----------------+-----------+----------------------
 Mountain                  |           1378 |       317 | 0.000608523034847594
 Skullclamp                |            628 |       159 | 0.000552579332479182
 Island                    |           1482 |       332 |  0.00048541454014803
 Sunken Hollow             |            209 |        64 | 0.000393843459986351
 Exotic Orchard            |            389 |       100 | 0.000370904245958032
 Windfall                  |            222 |        65 | 0.000361881986809246

donaldpminer

December 26th, 2017, EDHREC's 231529 decks

Count of how many decks we have for that commander along with how many times that card appears in the 99

commander            | cmdrcount | cardcount 
---------------------------------+-----------+-----------
 Atraxa, Praetors' Voice         |      4169 |       632
 Meren of Clan Nel Toth          |      2955 |      3064
 Breya, Etherium Shaper          |      2936 |       284
 Oloro, Ageless Ascetic          |      2648 |       124

donaldpminer

Unstable looks like a blast. Shenanigans everywhere. If only there was a casual-focused format dedicated to fun and silliness to play them in.

...

Hey, wait, we have one of those!

Until the next banned list announcement (January 15), silver-bordered cards are legal in Commander.

Yes, you heard that right. Dust off those Fruitcake Elementals and unleash the unsanity! It’s time to celebrate Unstable.

donaldpminer

Average EDH deck price by color identity.
Pulled from EDHREC data November 27, 2017. Based on Cardkingdom prices.

 coloridentity |       avg        | count 
---------------+------------------+-------
 {R,W}         | 302.720263810197 |  5648
 {G,R}         | 318.976849186062 |  7433
 {W}           | 325.784301387138 |  7930
 {B,R}         | 345.024110499027 |  6172
 {R}           |  345.14674354152 | 10103

donaldpminer

Number of decks per commander in EDHREC, July 31st 2017

Atraxa, Praetors' Voice|3299
Breya, Etherium Shaper|2342
Meren of Clan Nel Toth|2311
Oloro, Ageless Ascetic|2081
Nekusar, the Mindrazer|1791
Yidris, Maelstrom Wielder|1742
Kaalia of the Vast|1525
Alesha, Who Smiles at Death|1476

donaldpminer

Here are the lowest 100 used commanders according to EDHREC.
However, I am not including the commanders that have 0 decks.


SELECT commander, COUNT(*) FROM decks GROUP BY commander ORDER BY count LIMIT 100;
         commander          | count 
----------------------------+-------
 Jiwari, the Earth Aflame   |     1
 Autumn-Tail, Kitsune Sage  |     1
 Tuknir Deathlock           |     1