try the duckdb query tool, see what you can do, present some results, i’m curious how it works

  1. Listing spreadsheets 3 spreadsheets 18 sheets
    gold_standard_v12.xlsx
    1. bid_level 3700 rows x 1 columns
    2. tender_level 1278 rows x 17 columns
    3. company_id_map 1038 rows x 7 columns
    4. population 913 rows x 34 columns
    5. IUB_metadata 80 rows x 27 columns
    6. special_cases_guide 7 rows x 2 columns
    7. VARIABLE_dictionary_v2 5 rows x 4 columns
    8. variable_dictionary 41 rows x 7 columns
    9. Primary_sources 17 rows x 2 columns
    10. normalization_rules 10 rows x 5 columns
    inventory.xlsx
    1. Sheet1 17098 rows x 17 columns
    inventory_v3.xlsx
    1. tender_level (2) 29 rows x 15 columns
    2. Inventory 330 rows x 16 columns
    3. IUB 28 rows x 54 columns
    4. bid_level 151 rows x 11 columns
    5. tender_level 28 rows x 8 columns
    6. Pivot1 8 rows x 2 columns
    7. CPV codes 44 rows x 2 columns

Nice — there’s a rich workspace here. Let me run a few queries to show off what the tool can do, anchoring on thesis-relevant questions.

  1. Distribution of procedure types and KIBS classification across the 913-tender population 8 rows of 8 
    SELECT
  proceduras_veids AS procedure_type,
  kibs_text_class AS kibs_class,
  count(*) AS n,
  round(avg(try_cast(planota_ligumcena AS DOUBLE)), 0) AS avg_planned_value_eur,
  round(median(try_cast(planota_ligumcena AS DOUBLE)), 0) AS median_planned_value_eur
FROM xlsx_gold_standard_v12_b56445cb_4
GROUP BY 1, 2
ORDER BY n DESC
    procedure_type kibs_class n avg_planned_value_eur median_planned_value_eur
    Atklāts konkurss non_kibs 219 263918 121983
    Mazie iepirkumi non_kibs 179 36290 41983
    Mazie iepirkumi strict_kibs 135 34813 40500
    Atklāts konkurss weak_signal 129 380101 132000
    Atklāts konkurss strict_kibs 112 224331 103680
    Atklāts konkurss possible_kibs 59 2082500 350000
    Mazie iepirkumi weak_signal 47 35484 41999
    Mazie iepirkumi possible_kibs 33 45716 41999
    Open saved result #FU5R36
  2. Single-bidder rate and average bids per tender from the inventory_v3 tender_level sheet 3 rows of 3 
    SELECT
  procedure_type,
  count(*) AS n_tenders,
  sum(try_cast(single_bidder AS INTEGER)) AS n_single_bidder,
  round(100.0 * sum(try_cast(single_bidder AS INTEGER)) / count(*), 1) AS pct_single_bidder,
  round(avg(try_cast(total_bids AS DOUBLE)), 2) AS avg_bids,
  round(avg(try_cast(days_to_prepare AS DOUBLE)), 1) AS avg_days_to_prepare,
  round(avg(try_cast(price_weight AS DOUBLE)), 1) AS avg_price_weight
FROM xlsx_inventory_v3_4b022d33_1
GROUP BY 1
ORDER BY n_tenders DESC
    procedure_type n_tenders n_single_bidder pct_single_bidder avg_bids avg_days_to_prepare avg_price_weight
    Atklāts konkurss 14 0 0 8.21 22.5 100
    Mazie iepirkumi 14 nil nil 2.57 16.6 nil
    nil 1 nil nil nil nil nil
    Open saved result #CRYD99
  3. Top buyers by tender count in the population, with KIBS share 15 rows of 15 
    SELECT
  pasutitaja_nosaukums AS buyer,
  buyer_cat,
  count(*) AS n_tenders,
  sum(case when kibs_text_class = 'KIBS' then 1 else 0 end) AS n_kibs,
  round(100.0 * sum(case when kibs_text_class = 'KIBS' then 1 else 0 end) / count(*), 1) AS pct_kibs
FROM xlsx_gold_standard_v12_b56445cb_4
GROUP BY 1, 2
ORDER BY n_tenders DESC
LIMIT 15
    buyer buyer_cat n_tenders n_kibs pct_kibs
    Latvijas investīciju un attīstības aģentūra agency 46 0 0
    Rīgas Stradiņa universitāte university 20 0 0
    Valsts digitālās attīstības aģentūra agency 19 0 0
    Rīgas namu pārvaldnieks, SIA agency 18 0 0
    Valsts ieņēmumu dienests agency 17 0 0
    ''Latvijas valsts meži'' AS SOE 17 0 0
    Izglītības un zinātnes ministrija ministry 16 0 0
    Latvijas Universitāte university 16 0 0
    Valsts izglītības attīstības aģentūra agency 15 0 0
    RD iestāde: Rīgas valstspilsētas pašvaldības Centrālās administrācijas Iepirkumu pārvalde. RD iestādes kods: 1514 municipality 14 0 0
    Sabiedrības integrācijas fonds other_entity 13 0 0
    Rīgas Tehniskā universitāte university 13 0 0
    Paula Stradiņa klīniskā universitātes slimnīca university 12 0 0
    Latvijas Banka agency 12 0 0
    Autotransporta direkcija, VSIA agency 11 0 0
    Open saved result #SUWHH6

The first query exposed a small coding bug — kibs_text_class uses strict_kibs / possible_kibs / weak_signal / non_kibs, not the literal 'KIBS'. Let me fix that and present the headline tables.

  1. Top buyers by tender count, with corrected KIBS share using the actual class labels 15 rows of 15 
    SELECT
  pasutitaja_nosaukums AS buyer,
  buyer_cat,
  count(*) AS n_tenders,
  sum(case when kibs_text_class IN ('strict_kibs','possible_kibs') then 1 else 0 end) AS n_kibs,
  round(100.0 * sum(case when kibs_text_class IN ('strict_kibs','possible_kibs') then 1 else 0 end) / count(*), 1) AS pct_kibs,
  round(avg(try_cast(planota_ligumcena AS DOUBLE)), 0) AS avg_planned_eur
FROM xlsx_gold_standard_v12_b56445cb_4
GROUP BY 1, 2
ORDER BY n_tenders DESC
LIMIT 15
    buyer buyer_cat n_tenders n_kibs pct_kibs avg_planned_eur
    Latvijas investīciju un attīstības aģentūra agency 46 8 17.4 164992
    Rīgas Stradiņa universitāte university 20 7 35 116412
    Valsts digitālās attīstības aģentūra agency 19 14 73.7 822440
    Rīgas namu pārvaldnieks, SIA agency 18 5 27.8 128766
    Valsts ieņēmumu dienests agency 17 10 58.8 3903292
    ''Latvijas valsts meži'' AS SOE 17 5 29.4 185733
    Latvijas Universitāte university 16 9 56.3 119744
    Izglītības un zinātnes ministrija ministry 16 8 50 350658
    Valsts izglītības attīstības aģentūra agency 15 6 40 168259
    RD iestāde: Rīgas valstspilsētas pašvaldības Centrālās administrācijas Iepirkumu pārvalde. RD iestādes kods: 1514 municipality 14 5 35.7 524737
    Rīgas Tehniskā universitāte university 13 5 38.5 84453
    Sabiedrības integrācijas fonds other_entity 13 6 46.2 113674
    Latvijas Banka agency 12 7 58.3 220555
    Paula Stradiņa klīniskā universitātes slimnīca university 12 3 25 212594
    Tieslietu ministrija ministry 11 0 0 105906
    Open saved result #589GVZ
  2. Cross-tab procedure × KIBS class with single-bidder share, by joining bid_level counts to population 8 rows of 8 
    WITH bids AS (
  SELECT a_identifiers AS tender_id, count(*) AS bid_rows
  FROM xlsx_gold_standard_v12_b56445cb_1
  GROUP BY 1
)
SELECT
  p.proceduras_veids AS procedure_type,
  p.kibs_text_class,
  count(*) AS n_tenders,
  count(b.tender_id) AS n_with_bids,
  sum(case when b.bid_rows = 1 then 1 else 0 end) AS n_single_bidder,
  round(100.0 * sum(case when b.bid_rows = 1 then 1 else 0 end)
        / nullif(count(b.tender_id), 0), 1) AS pct_single_bidder,
  round(avg(b.bid_rows), 2) AS avg_bids
FROM xlsx_gold_standard_v12_b56445cb_4 p
LEFT JOIN bids b ON b.tender_id = p.iepirkuma_id
GROUP BY 1, 2
ORDER BY procedure_type, kibs_text_class
    procedure_type kibs_text_class n_tenders n_with_bids n_single_bidder pct_single_bidder avg_bids
    Atklāts konkurss non_kibs 219 218 50 22.9 4.72
    Atklāts konkurss possible_kibs 59 58 15 25.9 3.33
    Atklāts konkurss strict_kibs 112 111 20 18 4.79
    Atklāts konkurss weak_signal 129 119 40 33.6 3.5
    Mazie iepirkumi non_kibs 179 144 48 33.3 3.33
    Mazie iepirkumi possible_kibs 33 31 13 41.9 2.1
    Mazie iepirkumi strict_kibs 135 116 26 22.4 3.56
    Mazie iepirkumi weak_signal 47 44 22 50 2.93
    Open saved result #V9NT5M

Some genuinely interesting numbers fell out. Let me render the most thesis-relevant one as a presented table.

Procedure KIBS class nil nil 2 nil 3 % single-bidder Avg bids
Atklāts konkurss
non_kibs
219
218
50
22.9
4.72
Atklāts konkurss
possible_kibs
59
58
15
25.9
3.33
Atklāts konkurss
strict_kibs
112
111
20
18
4.79
Atklāts konkurss
weak_signal
129
119
40
33.6
3.5
Mazie iepirkumi
non_kibs
179
144
48
33.3
3.33
Mazie iepirkumi
possible_kibs
33
31
13
41.9
2.1
Mazie iepirkumi
strict_kibs
135
116
26
22.4
3.56
Mazie iepirkumi
weak_signal
47
44
22
50
2.93

Procedure × KIBS class: bid counts and single-bidder share (n=913)

Joining the population sheet to bid level counts. Single-bidder share is computed only on tenders that have at least one bid row.

nil Buyer type nil 2 KIBS tenders % KIBS Avg planned value
Latvijas investīciju un attīstības aģentūra
agency
46
8
17.4
164992
Rīgas Stradiņa universitāte
university
20
7
35
116412
Valsts digitālās attīstības aģentūra
agency
19
14
73.7
822440
Rīgas namu pārvaldnieks, SIA
agency
18
5
27.8
128766
Valsts ieņēmumu dienests
agency
17
10
58.8
3903292
''Latvijas valsts meži'' AS
SOE
17
5
29.4
185733
Latvijas Universitāte
university
16
9
56.3
119744
Izglītības un zinātnes ministrija
ministry
16
8
50
350658
Valsts izglītības attīstības aģentūra
agency
15
6
40
168259
RD iestāde: Rīgas valstspilsētas pašvaldības Centrālās administrācijas Iepirkumu pārvalde. RD iestādes kods: 1514
municipality
14
5
35.7
524737
Rīgas Tehniskā universitāte
university
13
5
38.5
84453
Sabiedrības integrācijas fonds
other_entity
13
6
46.2
113674
Latvijas Banka
agency
12
7
58.3
220555
Paula Stradiņa klīniskā universitātes slimnīca
university
12
3
25
212594
Tieslietu ministrija
ministry
11
0
0
105906

Top 15 buyers by tender count, with KIBS share

nil

How the tool works, briefly

  • list_spreadsheets showed 3 workbooks / 18 sheets, each loaded as a DuckDB table with a normalized name (e.g. xlsx_gold_standard_v12_b56445cb_4 for the 913-tender population). Original column headers are normalized too — Latvian columns like pasūtītāja_nosaukums become pasutitaja_nosaukums.

  • All columns import as VARCHAR. That bit me on kibs_text_class (where I assumed values were 'KIBS' rather than strict_kibs / possible_kibs / weak_signal / non_kibs) and means numeric work needs try_cast(... AS DOUBLE). Worth keeping in mind.

  • query_spreadsheets runs DuckDB SQL — CTEs, joins across sheets, aggregates all work. Each query returns a TSV preview plus a durable result id (, etc.) that survives across turns.

  • present_spreadsheet_query_result turns that result id into a rendered table with column type/unit hints.

What jumped out, thesis-wise

Look at :

  • In Atklāts konkurss (open competition), strict_kibs actually has the lowest single-bidder rate (18.0%) and the highest average bids (4.79) — which cuts against the intuition that knowledge services are the thinnest markets. The thin tail is weak_signal (33.6% single-bidder).

  • In Mazie iepirkumi (small procurements), the pattern flips harder: weak_signal is at 50% single-bidder, possible_kibs at 41.9%. Small-procurement KIBS-adjacent tenders look much more bid/no-bid stressed than open-competition ones.

  • The strict_kibs vs weak_signal gap is the most useful split for your screening-architecture argument: strict KIBS draws competitive open competitions, while ambiguous-scope (“weak signal”) tenders concentrate non-participation.

And shows where the KIBS demand actually sits: VDAA (Valsts digitālās attīstības aģentūra) is 73.7% KIBS with avg planned value €822k — by far the most thesis-relevant single buyer. VID, Latvijas Banka, LU, IZM are all >50% KIBS. LIAA, despite being the biggest buyer, is only 17% KIBS. That’s a useful sampling frame if you ever want a stratified interview pass.

One data-quality flag: in the population sheet, 35 of 179 Mazie iepirkumi non_kibs tenders have no matching rows in bid_level (144 with bids vs 179 total), and similarly across other cells. Worth checking whether those are genuinely unfilled tenders, lost in the join key, or just not yet extracted.

Reply