Lippukauppa ja e-liput
Montako e-lipputilausta on lunastettu tiettynä päivänä
python manage.py shell
from lippukala.models import Order from dateutil.tz import tzlocal from datetime import datetime tz = tzlocal() loerdag_start = datetime(2015, 9, 5, 0, 0, 0, tzinfo=tz) soendag_start = datetime(2015, 9, 6, 0, 0, 0, tzinfo=tz) Order.objects.filter(code__used_on__gte=loerdag_start, code__used_on__lt=soendag_start).distinct().count() # 2600
Lunastettujen koodien määrä per minuutti tietyllä aikavälillä
SELECT date_trunc('minutes', used_on) as ts, count(order_id) FROM lippukala_code WHERE used_on between '2015-09-05 00:07:00'::timestamp AND '2015-09-05 14:00:00'::timestamp GROUP BY ts ORDER BY ts;
Lunastettujen tilausten määrä per minuutti tietyllä aikavälillä
SELECT date_trunc('minutes', used_on) as ts, count(*) FROM lippukala_order o, lippukala_code c WHERE o.id=c.id AND c.used_on between '2015-09-05 00:07:00'::timestamp AND '2015-09-05 14:00:00'::timestamp GROUP BY ts ORDER BY ts;
Minkä kokoisia tilauksia ihmiset tilaa
python manage.py shell
In [6]: ops = OrderProduct.objects.filter( ...: order__event__slug='tracon2017', ...: order__confirm_time__isnull=False, ...: order__payment_date__isnull=False, ...: order__cancellation_time__isnull=True, ...: count__gte=1, ...: product__name__icontains='viikonloppu', ...: ) In [7]: ops.count() Out[7]: 2170 In [8]: c = Counter() In [9]: for op in ops: ...: c[op.count] += 1 ...: In [10]: c Out[10]: Counter({1: 1171, 2: 665, 3: 211, 4: 76, 5: 36, 6: 6, 7: 3, 8: 1, 10: 1})
Ohjelmanumerot, joista puuttuu ohjelmanjärjestäjän sähköpostiosoite
python manage.py shell
from programme.models import Programme for programme in Programme.objects.filter(category__event__slug='traconx', programmerole__person__email='').distinct(): print programme.title
Listaa kaikki taulut, joilla on vierasavain core_personiin
SELECT tc.constraint_name, tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name = 'core_person'; constraint_name | table_name | column_name | foreign_table_name | foreign_column_name -----------------------------------------------------------------+------------------------------------------+-------------+--------------------+--------------------- access_emailalias_person_id_d36631ab_fk_core_person_id | access_emailalias | person_id | core_person | id access_grantedprivi_person_id_9ef28127a5df59f_fk_core_person_id | access_grantedprivilege | person_id | core_person | id access_smtppassword_person_id_89672f1a_fk_core_person_id | access_smtppassword | person_id | core_person | id badges_badge_person_id_4cca74fd_fk_core_person_id | badges_badge | person_id | core_person | id desucon2016_signupextrav2_person_id_29c53c5d_fk_core_person_id | desucon2016_signupextrav2 | person_id | core_person | id desucon2017_signupextra_person_id_6c671a8f_fk_core_person_id | desucon2017_signupextra | person_id | core_person | id desuprofile_integr_person_id_37c6ed018d41d481_fk_core_person_id | desuprofile_integration_confirmationcode | person_id | core_person | id enrollment_enrollment_person_id_7567f500_fk_core_person_id | enrollment_enrollment | person_id | core_person | id event_log_entry_person_id_b043f99f_fk_core_person_id | event_log_entry | person_id | core_person | id frostbite2017_signupextra_person_id_13d02157_fk_core_person_id | frostbite2017_signupextra | person_id | core_person | id frostbite2018_signupextra_person_id_1962eecb_fk_core_person_id | frostbite2018_signupextra | person_id | core_person | id kawacon2017_signupextra_person_id_2b2304ea_fk_core_person_id | kawacon2017_signupextra | person_id | core_person | id kuplii2017_signupextra_person_id_b9148d52_fk_core_person_id | kuplii2017_signupextra | person_id | core_person | id kuplii2018_signupextra_person_id_e53a0420_fk_core_person_id | kuplii2018_signupextra | person_id | core_person | id labour_emptysignupextra_person_id_1927c3e9_fk_core_person_id | labour_emptysignupextra | person_id | core_person | id labour_signup_person_id_69459f4c_fk_core_person_id | labour_signup | person_id | core_person | id labour_surveyrecord_person_id_796a7ef7_fk_core_person_id | labour_surveyrecord | person_id | core_person | id membership_members_person_id_6f2156d9e989a345_fk_core_person_id | membership_membership | person_id | core_person | id nippori2017_signupextra_person_id_d78ca762_fk_core_person_id | nippori2017_signupextra | person_id | core_person | id person_id_refs_id_1b14485d | labour_personqualification | person_id | core_person | id person_id_refs_id_5fd30fad | core_passwordresettoken | person_id | core_person | id person_id_refs_id_6aca4060 | mailings_personmessage | person_id | core_person | id person_id_refs_id_e80a198a | core_emailverificationtoken | person_id | core_person | id popcult2017_signupextra_person_id_a6b1ea92_fk_core_person_id | popcult2017_signupextra | person_id | core_person | id programme_programmefeedbac_author_id_2b02e587_fk_core_person_id | programme_programmefeedback | author_id | core_person | id programme_programmerole_person_id_c6c414c6_fk_core_person_id | programme_programmerole | person_id | core_person | id shippocon2016_signupextra_person_id_19fe9525_fk_core_person_id | shippocon2016_signupextra | person_id | core_person | id tracon11_signupextrav2_person_id_715ff1f2_fk_core_person_id | tracon11_signupextrav2 | person_id | core_person | id tracon2017_signupextra_person_id_c74ce6f6_fk_core_person_id | tracon2017_signupextra | person_id | core_person | id tracon2018_signupextra_person_id_907feec9_fk_core_person_id | tracon2018_signupextra | person_id | core_person | id tylycon2017_signupextra_person_id_cde12b10_fk_core_person_id | tylycon2017_signupextra | person_id | core_person | id worldcon75_signupextra_person_id_36c0d651_fk_core_person_id | worldcon75_signupextra | person_id | core_person | id yukicon2017_signupextra_person_id_73d7b8f4_fk_core_person_id | yukicon2017_signupextra | person_id | core_person | id yukicon2018_signupextra_person_id_17b24a02_fk_core_person_id | yukicon2018_signupextra | person_id | core_person | id intra_teammember_person_id_d1a2a240_fk_core_person_id | intra_teammember | person_id | core_person | id (35 rows)